• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 663
  • Last Modified:

BULK INSERT format file with undelimited data

Experts,

I need to import a flat txt-file with fixed-width columns into a database using BULK INSERT. Can I simply use the BCP format file to do this? What would the file look like?
0
HenningF
Asked:
HenningF
  • 7
  • 6
1 Solution
 
HenningFAuthor Commented:
This is basically an article from BOL which I allready read but from what I can see it doesn't cover the case where the data-file doesn't have any column delimiters...? Or is it me that just doesn't understand exactly how this thing works?
0
 
HenningFAuthor Commented:
here's a simple example file with the columns id, created and last_updated:
---------------------------------------------
000000154872006062020060623
000000154882006062020060625
000000154902006062021060622
---------------------------------------------

What would the format-file look like for this kind of data?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Vadim RappCommented:
> I can see it doesn't cover the case where the data-file doesn't have any column delimiters...

From the description in the link "fuield terminator": "Null terminator (no visible terminator) \0 "

1 sqlchar 0 11 \0 1 id
2 sqlchar 0 08 \0 2 created
3 sqlchar 0 08 \0 3 last_updated
0
 
HenningFAuthor Commented:
Ok, great! I set up some testing here but I got an error message which doesn't make sense to me...do you have a clue? The database is SQL Server 2000 Std.

"Invalid data type for column number 1 in the format file "C:\Temp\bulk\bulk_format.txt"."

Format File:
---------------------------------------------
8.0
3
1      sqlchar      0      11      \0      1      id
2      sqlchar      0      08      \0      2      created
3      sqlchar      0      08      \0      3      last_updated
---------------------------------------------

Data file:
---------------------------------------------
000000154872006062020060623
000000154882006062020060625
000000154902006062021060622
---------------------------------------------

Database table
---------------------------------------------
CREATE TABLE [dbo].[bulktest](
      [ID] [char](11),
      [Created] [char](8),
      [Last_updated] [char](8)
)
---------------------------------------------

SQL statement:
---------------------------------------------
BULK INSERT testing.dbo.bulktest
FROM 'C:\Temp\bulk\bulk.txt' WITH (FORMATFILE='C:\Temp\bulk\bulk_format.txt');
---------------------------------------------
0
 
Vadim RappCommented:
this has to do something with row terminator. If you merge your lines in the inpud file into one, it will work. Will try more.
0
 
Vadim RappCommented:
for the last row, specify terminator as \n

8.0
3
1     SQLCHAR     0     11     ""     1     id ""
2     SQLCHAR     0     08     ""     2     created ""
3     SQLCHAR     0     08     "\n"     3     last_updated ""
0
 
Vadim RappCommented:
CORRECTION: "for the last row" -> "for the last FIELD"
0
 
HenningFAuthor Commented:
Hm, I don't get it... :

Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 3 in format file 'C:\Temp\bulk\bulk_format.txt'

I have tried adding the default database collation also which is Danish_Norwegian_CI_AS but without any luck.
0
 
HenningFAuthor Commented:
I gotta go home now...have been working 2 hours of overtime with this one but I'l check back in first thing tomorrow. Thank you very much for your help so far! :)
0
 
Vadim RappCommented:
collation name is the empty string after the column name:

last_updated ""
0
 
HenningFAuthor Commented:
Ok, it seems I can't get this to work if my life depended on it! Making me furious because I belive this is simple but it just doesn't work. Have you actually tried the code at your end and made it work? I'm just curious because it sure doesn't work here...

This is what I got:

SQL:
BULK INSERT bulktest
FROM 'C:\Temp\bulk\bulk.txt' WITH (FORMATFILE='C:\Temp\bulk\bulk_format.txt');

Bulk.txt:
000000154872006062020060623
000000154882006062020060625
000000154902006062021060622

Bulk_format.txt:
8.0
3
1      SQLCHAR      0      11      ""      1      id      Danish_Norwegian_CI_AS
2      SQLCHAR      0      08      ""      2      created      Danish_Norwegian_CI_AS
3      SQLCHAR      0      08      "\r\n"      3      updated      Danish_Norwegian_CI_AS

I have tried setting version 7 and removing collation, terminators \r and \n together and by themselves, separating the settings in the format file with single-space and tab...is it possible that you do me the favor and try the code yourself? I'll double the points if you help me out on this one... :)
0
 
HenningFAuthor Commented:
AAAAAAAAAAAAAAAAARRRRRRRRRRRRRRRRRRRRRRRGGGGGGGGGGGGGGGGGGGGHHHHHHHHHHHHHHHHH!!!

I just found the problem and it the most annoying thing ever!! I kept getting error messages no matter what I did and now, after several hours, for some reason I hit "enter" and put a blank line after the last column in the format file and that made the difference. To be honest it pisses me off...and it almost ruins the pleasure of finally making it work. But still; thanx alot for your help buddy...I couldn't have done it without you :)
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now