Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

How to efficiently import data in .rpt format into SQL 2008 Expand / Collapse
Author
Message
Posted Thursday, July 17, 2014 7:10 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:05 PM
Points: 808, Visits: 1,445
Good day,

I have been told to import 100G data generated in SSMS and in .rpt format (10+ files) back to another SQL2008R2 server, I am wondering what would be the best practice to do this in a one time basis.

Thank you in advance, any suggestion is appreciated.
Post #1593578
Posted Thursday, July 17, 2014 8:53 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:05 PM
Points: 808, Visits: 1,445
I should have mentioned in the OP that I can't ask for a different format at this moment, I need to get this done for this batch of data.

Thanks.
Post #1593653
Posted Thursday, July 17, 2014 9:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 3,167, Visits: 8,095
I would have to see the file format before even thinking about options.



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #1593666
Posted Thursday, July 17, 2014 10:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,098, Visits: 3,155
Since it's from SSMS, it's likely just a standard text file .rpt, rather than a Crystal Reports .rpt.

I suggest using SSIS to import that data. It will of course be a fairly long process to read, interpret and load 100GB of text data.


SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1593699
Posted Thursday, July 17, 2014 10:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:30 PM
Points: 12,918, Visits: 32,089
if it is plain text, i'd just use bulk insert instead of building an SSIS package;
for me, that would be faster than anything else.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1593702
Posted Thursday, July 17, 2014 1:33 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,098, Visits: 3,155
Lowell (7/17/2014)
if it is plain text, i'd just use bulk insert instead of building an SSIS package;
for me, that would be faster than anything else.


I'm thinking you're gonna have to do some serious manipulation on that .rpt file to load it to SQL tables, in which case I'd want the transformation capabilities of SSIS.


SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1593813
Posted Thursday, July 17, 2014 1:51 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:05 PM
Points: 808, Visits: 1,445
ScottPletcher (7/17/2014)
Since it's from SSMS, it's likely just a standard text file .rpt, rather than a Crystal Reports .rpt.

I suggest using SSIS to import that data. It will of course be a fairly long process to read, interpret and load 100GB of text data.


It is plain text although the file extension is very misleading .rpt

I decide to take your approach and here is what I've done and what I am having issue:

raw data (test.csv):
326567      98          1026             11          35638468    3232282837           2259294439           2886995029           1                    395                  0
281578 9 1026 11 48210209 3232282839 3247785220 2886998653 1 119 0


Here is the format file:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharFixed" LENGTH="12"/>
<FIELD ID="2" xsi:type="CharFixed" LENGTH="12"/>
<FIELD ID="3" xsi:type="CharFixed" LENGTH="17"/>
<FIELD ID="4" xsi:type="CharFixed" LENGTH="12"/>
<FIELD ID="5" xsi:type="CharFixed" LENGTH="12"/>
<FIELD ID="6" xsi:type="CharFixed" LENGTH="21"/>
<FIELD ID="7" xsi:type="CharFixed" LENGTH="21"/>
<FIELD ID="8" xsi:type="CharFixed" LENGTH="21"/>
<FIELD ID="9" xsi:type="CharFixed" LENGTH="21"/>
<FIELD ID="10" xsi:type="CharFixed" LENGTH="21"/>
<FIELD ID="11" xsi:type="CharFixed" LENGTH="20"/>
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\r"
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Field1" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="2" NAME="Field2" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="3" NAME="Field3" xsi:type="SQLINT" LENGTH="17"/>
<COLUMN SOURCE="4" NAME="Field4" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="5" NAME="Field5" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="6" NAME="Field6" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="7" NAME="Field7" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="8" NAME="Field8" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="9" NAME="Field9" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="10" NAME="Field10" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="11" NAME="Field11" xsi:type="SQLINT" LENGTH="20"/>
</ROW>
</BCPFORMAT>


Here is the table's schema:
CREATE TABLE [dbo].[LogDB](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Field1] [int] NULL,
[Field2] [bigint] NULL,
[Field3] [bigint] NULL,
[Field4] [bigint] NULL,
[Field5] [bigint] NULL,
[Field6] [bigint] NULL,
[Field7] [bigint] NULL,
[Field8] [bigint] NULL,
[Field9] [bigint] NULL,
[Field10] [bigint] NULL,
[Field11] [bigint] NULL,
CONSTRAINT [PK_LogDB] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Here is my script:
BULK INSERT Ticket.dbo.LogDB
FROM 'C:\temp\test.csv'
WITH
(
FORMATFILE = 'C:\temp\format.xml'
);


It prompts me this error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

How do I fix it? Thank you so much for your help.

BTW: the original raw data file .rpt contains column names in its first line and separated line as the second line. Is there an option in Bulk input that can automatically generate the table based on the raw data file's name as well as the table's column names?
Post #1593822
Posted Thursday, July 17, 2014 1:58 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,098, Visits: 3,155
halifaxdal (7/17/2014)
ScottPletcher (7/17/2014)
Since it's from SSMS, it's likely just a standard text file .rpt, rather than a Crystal Reports .rpt.

I suggest using SSIS to import that data. It will of course be a fairly long process to read, interpret and load 100GB of text data.


It is plain text although the file extension is very misleading .rpt

I decide to take your approach and here is what I've done and what I am having issue:

raw data (test.csv):
326567      98          1026             11          35638468    3232282837           2259294439           2886995029           1                    395                  0
281578 9 1026 11 48210209 3232282839 3247785220 2886998653 1 119 0


Here is the format file:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharFixed" LENGTH="12"/>
<FIELD ID="2" xsi:type="CharFixed" LENGTH="12"/>
<FIELD ID="3" xsi:type="CharFixed" LENGTH="17"/>
<FIELD ID="4" xsi:type="CharFixed" LENGTH="12"/>
<FIELD ID="5" xsi:type="CharFixed" LENGTH="12"/>
<FIELD ID="6" xsi:type="CharFixed" LENGTH="21"/>
<FIELD ID="7" xsi:type="CharFixed" LENGTH="21"/>
<FIELD ID="8" xsi:type="CharFixed" LENGTH="21"/>
<FIELD ID="9" xsi:type="CharFixed" LENGTH="21"/>
<FIELD ID="10" xsi:type="CharFixed" LENGTH="21"/>
<FIELD ID="11" xsi:type="CharFixed" LENGTH="20"/>
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\r"
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Field1" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="2" NAME="Field2" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="3" NAME="Field3" xsi:type="SQLINT" LENGTH="17"/>
<COLUMN SOURCE="4" NAME="Field4" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="5" NAME="Field5" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="6" NAME="Field6" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="7" NAME="Field7" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="8" NAME="Field8" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="9" NAME="Field9" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="10" NAME="Field10" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="11" NAME="Field11" xsi:type="SQLINT" LENGTH="20"/>
</ROW>
</BCPFORMAT>


Here is the table's schema:
CREATE TABLE [dbo].[LogDB](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Field1] [int] NULL,
[Field2] [bigint] NULL,
[Field3] [bigint] NULL,
[Field4] [bigint] NULL,
[Field5] [bigint] NULL,
[Field6] [bigint] NULL,
[Field7] [bigint] NULL,
[Field8] [bigint] NULL,
[Field9] [bigint] NULL,
[Field10] [bigint] NULL,
[Field11] [bigint] NULL,
CONSTRAINT [PK_LogDB] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Here is my script:
BULK INSERT Ticket.dbo.LogDB
FROM 'C:\temp\test.csv'
WITH
(
FORMATFILE = 'C:\temp\format.xml'
);


It prompts me this error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

How do I fix it? Thank you so much for your help.

BTW: the original raw data file .rpt contains column names in its first line and separated line as the second line. Is there an option in Bulk input that can automatically generate the table based on the raw data file's name as well as the table's column names?



I suspect the termination is \r rather than just \r. But also verify all the data lengths, just to be sure:
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\r"


SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1593825
Posted Thursday, July 17, 2014 2:10 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:05 PM
Points: 808, Visits: 1,445
Thanks ScottPletcher, I removed and also removed the last line which shows some info like "totally xxx rows"

The script returns me error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

Where is the varchar from?
The raw data does have lots extra big numbers and I just cannot use varchar for those fields.
Post #1593834
Posted Thursday, July 17, 2014 2:32 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:05 PM
Points: 808, Visits: 1,445
I got some hints when I tried another file, it seems the first couple characters in the raw data file has some non-numeric character so the script throws error like:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '326567 ' to data type int.

Those three characters are EF BB EF, what are they for? the file is in ANSI/ASCII format.

I also tried to save the raw file in a different format UTF-8, same error.
If I save the file as Unicode, EF BB EF were removed, but then I got error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

As I can inspect each character in hex code, I suspect that the hex 20 (which means blank space) is causing the issue
Post #1593841
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse