sql server - How to open a rpt file as a SQL Table?

Question

I have a rpt dump that one of my colleagues gave. I need to open this file as a SQL Table (I presume that this is possible since he generated the rpt dump from the SQL Table).

How do I do that. I am using SQL Server Management Studio. I can also open the rpt file as a separate file in SSMS.

Answer 1

Though this answer is late, I didn't see a canonical answer to the problem of opening the .rpt file format and writing it to a SQL table.

1. In SQL Server Management Studio in Object Explorer, right click on the database you want to load the file into and choose Tasks... Import Data.

2. Choose Flat File Source, select your file, then ensure the screen is setup to match the following screenshot (Code page=65001 UTF-8, Format=Ragged right, Column names in the first data row). Then click Next.

3. Click on the first character of each column (see the yellow highlights in the screenshot below for where to click) to insert columns at the appropriate spots:

4. Click the Advanced tab on the left. Go through each column name and remove the spaces from the end of the column name. Fix the data types as you see fit. Warning the first few columns may need to be strings because of the "(NNN affected rows)" file footer.

5. Go to the Preview tab on the left, change the "Data rows to skip" to 1 and click Refresh:

6. Click the Next button and setup your connection to your SQL database:

7. Click next and type in the name of your table in the format [dbo].[MyTable] then click Edit Mappings and confirm the column data types match. String (DT_STR) columns should be varchar columns here. Unicode string (DT_WSTR) should be nvarchar columns here.

8. Click Next and Finish to run immediately. You should see success messages not errors hopefully:

9. There is a footer which says "(NNN rows affected)" (where NNN is the row count of your file). Query the destination SQL table to find that row and delete it. select * from MyTable where MyColumn1 like '%(%' may help you find that row. Please confirm that query returns only one row before you delete it.