Bulk Insert (How do I find my rowterminator?)
-
I'm trying to use bulk insert and I can't for the life of me find my rowterminator. Is there a way to do this? I've tried all kinds of things as a rowterminator but I'm stabbing in the dark at this point.
When I use textpad and View/Visible Spaces it shows these upside down 'P' or flags at the end of each line. Help?
-
most files end in slash r if they came from unix/other systems, or slash n if they came from windows.
try changing your bulk insert to use those:
\n = vbCrLf = CHAR(13) + CHAR(10) = \n
\r vbCr = CHAR(13) = \r
CREATE TABLE BULKACT(RAWDATA VARCHAR (8000))
BULK INSERT BULKACT FROM 'c:\Export_o.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\r',
FIRSTROW = 1
)
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Okay so I'm running the following code to import my asterisk delimited file.
/* CREATE TEMP TABLE */IF OBJECT_ID('tempdb..#tblhdr830') IS NOT NULL DROP TABLE #tblhdr830
CREATE TABLE #tblhdr830(A1 varchar (max) not null
, A2 varchar (max) not null
, A3 varchar (max) not null
, A4 varchar (max) not null
, A5 varchar (max) not null
, A6 varchar (max) not null
, A7 varchar (max) not null
, A8 varchar (max) not null
, A9 varchar (max) not null
, A10 varchar (max) not null
, A11 varchar (max) not null
, A12 varchar (max) not null
, A13 varchar (max) not null
, A14 varchar (max) not null
, A15 varchar (max) not null
, A16 varchar (max) not null
, A17 varchar (max) not null
, A18 varchar (max) not null)
/* LOAD ALL DATA INTO TEMP TABLE */
declare @bulk varchar(100) = 'BULK INSERT #tblhdr830 FROM '''+@FileNamePath+''' WITH (FIELDTERMINATOR = ''*'', ROWTERMINATOR = '''')'
exec (@Bulk)
which runs okay except it doesn't seem to know where the line breaks or rowterminators are. When I add
DATAFILETYPE = ''char'',to the bulk insert so that it looks likedeclare @bulk varchar(100) = 'BULK INSERT #tblhdr830 FROM '''+@FileNamePath+''' WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''*'', ROWTERMINATOR = '''')'it gives meIncorrect syntax near ','.
.
-
Are you sure VARCHAR(100) is long enough for this second statement?
-
Great! I changed varchar(100) to varchar(1000) and it works without errors but I'm still not finding the rowterminator. I've tried
\r
\r
\r
char(10)
0x0a
and none of them seem to be able to find the end of the line. Like I said before when I open the file in textpad I show that this is an ANSI file with some sort of an upside down flag or 'P' at the end of each line when I turn on "visible spaces" if that helps.
-
try both slash r and slash n.
open the file in notepad++, which can give you the exact ascii character it is terminating in.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
That screenshot is exactly what notepad++ shows for my file "CRLF" but when I tried \r it didn't work. Maybe there's something more in my process or maybe I'm looking at this wrong. Let me try to give more detail.
when I pull the file it looks like this in notepad++
My code in SQL currently looks like this
/* CREATE TEMP TABLE */IF OBJECT_ID('tempdb..#tblhdr830') IS NOT NULL DROP TABLE #tblhdr830
CREATE TABLE #tblhdr830(A1 varchar (max) not null
, A2 varchar (max) not null
, A3 varchar (max) not null
, A4 varchar (max) not null
, A5 varchar (max) not null
, A6 varchar (max) not null
, A7 varchar (max) not null
, A8 varchar (max) not null
, A9 varchar (max) not null
, A10 varchar (max) not null
, A11 varchar (max) not null
, A12 varchar (max) not null
, A13 varchar (max) not null
, A14 varchar (max) not null
, A15 varchar (max) not null
, A16 varchar (max) not null
, A17 varchar (max) not null
, A18 varchar (max) not null)
/* LOAD ALL DATA INTO TEMP TABLE */
declare @bulk varchar(1000) = 'BULK INSERT #tblhdr830 FROM '''+@FileNamePath+''' WITH (FIELDTERMINATOR = ''*'', ROWTERMINATOR = '''')'
exec (@Bulk)
select * from #tblhdr830 and when I run it I get this and you can see that row 2 is connected in column A8 which makes me think that it's not recognizing the .
When I run the \r I get only one row returned.
-
Okay so now that I figured out how to add images I edited my last post. Please look it over and let me know what I'm missing.
-
Not only row 2 is connected but other rows are connected in column A10 as well.
Could you attach a sample text file? I would like to have a look at it myself.
EDIT:
I've just notieced that based on the example the rows in your file structure won't always have 18 columns. If i remember correct, with BULK INSERT the number of columns can't vary. To make this work you would have to fill up your shorter lines with extra field terminators so every row has 18 columns and also change your table structure and make columns nullable.
EXAMPLE:
-
How do I "fill up your shorter lines with extra field terminators so every row has 18 columns"?
Another alternative I was thinking was to import all of this data into one column and then let SQL parse it out into different columns based upon the first three digits of the file (that's what determines how many columns are needed).
Or can I do a bulk insert with a where statement? Then I can say bulk insert where substring(field,1,3) = 'ohd' or something like that?
-
You can't use WHERE with BULK INSERT.
Your alternative may be the way to go in this case.
-
If I'm not mistaken, this looks like an EDI text file you're trying to process. If that's not the case, ignore the rest of this post. 😉
Your different row types in the file are going to have different layouts and the number of columns is going to be different. For example, your detail row contains 2 fields and your header row contains a bunch of them. Other row types are going to have different ones as well. Your trailing row is going to have very few. With the number of fields in a row so variable, it's going to cause problems, especially if you have to process multiple document types.
A quick thought...
Have you considered using Jeff's DelimitedSplit8K? I've not implemented this as an EDI solution before, but I think I would have been much better than the environment that was dictated to me at the time. If you used BULK INSERT to get each whole line into a staging table, you could then use DelimitedSplit8K to break it out. You would know your document type from row 1 and column 1 of each row would tell you the row type. Then you'd know what column each field meant so you could create the appropriate rows in your destination table.
If you're starting on the project, it might be worth taking a look at this as a possibility.
-
ok , the issue is this is one of those file swhere there's layers of data;
three from your screenshot:
OHD record
HDR record
DTL record(s) that are related to the HDR row above it.
I've always processed items like this in a script task, rbar style, so that i can maintaint he reference to the HDR--DTL, but it could be done via TSQL as well.
you'd bulk insert into a staging table with a varchar(8000) and a trailing /last column of type int + identity(), and then process the rows via the delimiters seperately.
then you can use row_number() and partition by to split out HDR records from their DTL records.
CREATE TABLE BULKACT(RAWDATA VARCHAR (8000),Id int identity())
BULK INSERT BULKACT FROM 'c:\Export_o.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = '[~~]', --this does not exist int he data, so each row is grabbed whole
ROWTERMINATOR = '\r',
FIRSTROW = 1
)
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Now when i look at it, it actually looks like one of the EDI formats. I did have a chance to work with EDIFACT files before but i remember that there was one format that looked like the one in this topic.
-
Lowell (3/11/2015)
ok , the issue is this is one of those file swhere there's layers of data;three from your screenshot:
OHD record
HDR record
DTL record(s) that are related to the HDR row above it.
I've always processed items like this in a script task, rbar style, so that i can maintaint he reference to the HDR--DTL, but it could be done via TSQL as well.
you'd bulk insert into a staging table with a varchar(8000) and a trailing /last column of type int + identity(), and then process the rows via the delimiters seperately.
then you can use row_number() and partition by to split out HDR records from their DTL records.
CREATE TABLE BULKACT(RAWDATA VARCHAR (8000),Id int identity())
BULK INSERT BULKACT FROM 'c:\Export_o.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = '[~~]', --this does not exist int he data, so each row is grabbed whole
ROWTERMINATOR = '\r',
FIRSTROW = 1
)
Lowell I get an error with the identity() piece.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply