Format File Help

[Ebes1099]

I'm trying to create a Format File for a bulk insert into SQL because I have a comma delimited file with some currency fields that have a comma to separate the thousands and it's causing issues on import. The currency fields have a text qualifier of a ".

I've attempted to write an import file but I'm still getting some data type mismatch errors when importing. More specifically on the date field and the paid amount fields. I can get the date field to import if I make it a SQLCHAR and a varchar(20) in my SQL table, but I'm hoping to import it as a date. If I open my .csv files in Excel the date fields are all Data format and D/M/YYYY.

My Currency fields are stored as currency in Excel with - numbers in (). My table in SQL is created using the money data type for those fields.

There's also a units field that is stored as a number with a comma for a thousands separator so I'm assuming that has the same " text qualifier. (I think I missed that in my format file, but I'll update it to whatever I use for the currency fields as a delimiter).

I've uploaded an example of the data and my attempt at my format file. Any help is appreciated. Thanks.

 

[Ebes1099]

I forgot to attach the data example. Here it is.

 

[Ebes1099]

I've made a few tweaks and actually completed an upload. It still isn't working as expected though.

What I did was go into my CSV tables and format all of the currency fields as Number format without a comma separating the thousands and using the -1234.56 style for negative numbers. Same thing ofr my Units field.

Then I updated my Format File with some fixes I noticed it needed.

My problem now seems to be just isolated to the currency fields. They are all getting imported as extremely small numbers, like 3.99434248774146E-315. I used the money format at first and they were huge numbers, now I'm trying float and they are extremely small numbers. I look at the .csv file in Notepad and don't see how it's getting these numbers.

I'll attach both an updated data sample and format file again.

 

[Ebes1099]

Here's the .csv

 

[gmmastros]

All of the data types in the format file should be SQLCHAR. I say this because all of the data in your csv file is text (even if that text represents numbers).



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Ebes1099]

George,

Can I then create my table in SQL with data types of int/money/varchar and it will still import into them even if the format file says SQLCHAR for that field?

 

[gmmastros]

Yes. In the format file, you are basically saying that the data in the file is text. Basically... if you can open the file in notepad and read everything perfectly, then you use SQLCHAR in the format file.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Ebes1099]

Awesome, thank you sir.