Openrow set and Decimals that come in as integers. 2

[timroop]

We have a CSV file that is being imported with an
insert into #temptable select * from openrowset....

There is one field [Units] where the majority of the values are listed as integers. A few of them (1 out of 500) are decimal values (28.52). The decimal values are being reduced to integers (28). We have tried all sorts of tricks like predefining the temp table with the [units] field set as decimal(10,2) and by using Cast functions to get the values in as decimals. No joy in any of them.

We have noticed that the openrowset seems to get the data type from the first record in the file. If that record has decimal places then all of the records will have them. If it is an integer then again no joy.

Editing the file before import is not an option. How can openrowset be forced to return decimals?

Thank you.


Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[gmmastros]

What is the source of the OpenRowset ? Excel? If so, do a google search on [google]OpenRowset Excel IMEX[/google].

-George

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

 

[timroop]

It is not Excel. It is a text based CSV file. I did try the IMEX flag but it did not change the behavior.

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[RiverGuy]

If nothing else, declare [Unit] in your temp table as char, import the file, and then cast the value to a decimal either into an extra column or a new temp table.

 

[timroop]

RiverGuy,

We tried that too! It still cuts the decimal off of the value. It truly behaves like the first record value defines the datatype for the OpenRowset function for the CSV imports. I don't see a way to override this like the IMEX option for an Excel import.

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[RiverGuy]

I'm wondering if you would be able to use the Jet driver and then use the IMEX setting in the connection string. If you look on connectionstrings.com, you can see that you're able to use Jet with text files.

 

[gmmastros]

Wait a minute.

Instead of using openrowset, can I talk you in to using Bulk Insert.

For simple CSV files....

[COLOR=blue]Bulk[/color] [COLOR=blue]Insert[/color] #temptable [COLOR=blue]From[/color] [COLOR=red]'C:\Folder\Yourfile.csv'[/color]
[COLOR=blue]With[/color] (FieldTerminator = [COLOR=red]','[/color],
      RowTerminator = [COLOR=red]''[/color])

This gets a little messy if your column terminators are not simple commas. For example, I have a csv file I import from regularly that has quotes-comma-quote terminators for string columns and just commas for number columns. To accommodate this, I created a format file and used it in the Bulk Insert command. It works well. I encourage you to try this approach. It's likely to be more stable and faster too.

If you have a similar problem with the column delimiters, let me know and I will post a sample format file.

-George

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

 

[timroop]

I gave the Jet driver a run but the IMEX=1 didn't change the data. I guess we will run with the BulkInsert route. We did it that way before but I was hoping to standardize on one import method to make future coding simpler.

Thanks guys.

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva