3

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.

Spider Man
  • 415
  • 4
  • 10
  • 26
  • What is an `rpt` dump and how is it formatted? The rpt-tag doesn't have any information about that. – TT. Nov 29 '16 at 09:02

2 Answers2

13

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.

set Flat File Source and Ragged right

  1. 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:

define fixed width columns

  1. 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.

Fix column name trailing spaces and data types

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

Data rows to skip

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

Destination DB connection info

  1. 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.

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

Success

  1. 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.
GregGalloway
  • 11,355
  • 3
  • 16
  • 47
0

I was able to open the RPT file in Excel then save as CSV

Priyank_Vadi
  • 1,028
  • 10
  • 27
  • I need to open this file as a SQL Table so that I can write some queries and extract the information from the rows. Otherwise, I will have to parse the Excel file using Python pandas which will take a lot of time – Spider Man Nov 29 '16 at 06:31
  • I tried to do this and Excel said it was not a valid format. – TylerH Jan 25 '23 at 19:58
  • @TylerH do you have latest Excel version ? Then it should work. – Priyank_Vadi Jan 31 '23 at 06:46
  • 1
    @Priyank_Vadi Yes, I'm on M365 Build 2211 which was the latest as of a few days ago. But that shouldn't really matter, since I know you weren't using a version nearly that old back in 2016. – TylerH Jan 31 '23 at 14:59