FormatFile could not be read

[bmpsu]

SQL Server 9.0

I'm attempting to import data from a CSV file with double quotes around each filed. I created a formatfile and placed it in the same directory the CSV file is located. I have load files from this directory before, but without a formatfile. This is the error I receive in sql server.

Msg 4862, Level 16, State 1, Line 5
Cannot bulk load because the file "\\mylocation\ffLeads.txt" could not be read. Operating system error code (null).

I am assuming this is not an error within my formatfile since it doesn't seem like it gets to that point???

 

[bmpsu]

Should have mentioned that I am using the BULK INSERT command.

BULK INSERT ##TEST FROM '\\myLocation\myfile.csv' WITH
(
FORMATFILE = '\\mylocation\ffLeads.txt'
)

 

[gmmastros]

What output do you get when you run this?

[tt][blue]exec xp_fileexist '\\myLocation\myfile.csv'[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmpsu]

I ran this command against the csv file and the format file.

File Exists = 1
File is a Directory = 0
Parent Directory Exists = 1

...for both files.

 

[bmpsu]

This is an example of my formatfile:

9.0
27
1 SQLCHAR 0 50 "','" 1 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "','" 2 Fname SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "','" 3 Lname SQL_Latin1_General_CP1_CI_AS
...

 

[bmpsu]

Sorry for the multiple post. Here is a better example:

9.0
27
1 SQLCHAR 0 50 "','" 1 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "','" 2 Fname SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "','" 3 Lname SQL_Latin1_General_CP1_CI_AS
...
27 SQLCHAR 0 8 "'\r\n" 27 ConvertedDate SQL_Latin1_General_CP1_CI_AS


Data Example:
"1","Bill","Jones","01012001

 

[gmmastros]

[tt][blue]exec xp_fileexist '\\mylocation\ffLeads.txt'[/blue][/tt]

Sorry... I meant to have you run this on the format file.

Also... I've seen format files get weird because of the lst line in it. I don't remember which way it's supposed to be, but, I think there should NOT be any blank lines at the end of the format file. Open it in notepad, go the end of the file (end of the last line) and hit backspace until the cursor is at the end of the line with text on it.

Ex:

9.0
27
1  SQLCHAR  0  50  "','"  1  ID  SQL_Latin1_General_CP1_CI_AS
2  SQLCHAR  0  50  "','"  2  Fname  SQL_Latin1_General_CP1_CI_AS
3  SQLCHAR  0  50  "','"  3  Lname  SQL_Latin1_General_CP1_CI_AS

9.0
27
1  SQLCHAR  0  50  "','"  1  ID  SQL_Latin1_General_CP1_CI_AS
2  SQLCHAR  0  50  "','"  2  Fname  SQL_Latin1_General_CP1_CI_AS
3  SQLCHAR  0  50  "','"  3  Lname  SQL_Latin1_General_CP1_CI_AS
      <-- eventhough this is blank, the line shouldn't be here.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmpsu]

I checked for extra lines and spaces. Everything is clear there. I even tried to strip my csv file down to 1 row and 3 columns and the same changes to my format file.

 

[bmpsu]

I reference the FAQ of this forum (faq183-1602). I did reference this earlier, but thought I would be fancy and skip using the dummy server column names as stated in the FAQ. Got it working once I used the format in the FAQ.

 

[bmpsu]

That FAQ is great. Star to that person. Lesson learned; check out the FAQs, the solutions/suggestions are there for a reason.