Importing text file removes decimal separator

Hi,

I'm trying to import a semi-comma separated text file into a SQL db. I have a field in the text file that contains decimal number. As a decimal separator it's used a comma (15,35). When i use a DTS package to create a destination table and import all rows, the field is created as a float field. In this field the decimal comma is removed so the number in SQL becomes 1535. If I change the decimal separator to (.) i works OK. But I need to get it work with comma as decimal separator. In the DTS package the field form the text file is recognised as varchar (8000). Any ideas?

Ingar

You have to choose appropriate locale which threats comma as a decimal delimiter. There is the drop-down control for choosing locales on the first page of the Flat File Connection Manager UI.

Thanks.

|||

Hi, thanks for your answer. I can see that I didn't specify which SQL version i was using. I use SQL 2000 not 2005. I know that the theme in this forum is SSIS but it also states "transforms/data flow" and I thought DTS packages from SQL 2000 was included here. I appologize if I have misunderstood this but english is not my first language.

So as far as I know the Flat file connection manager is not available in SQL 2000.

But thanks anyway.

Ingar

importing data

we are trying to import data from a flat file using an uptick (`) as a column separator and {CR/LF} as a record terminator. There is a variable number of columns for each record. The initial record in the flat file has 3 columns. Upon processing this record, the import sets all records to 3 columns and does not read the column separators past the second column (even though there may be up to 7 columns in the record).

This method worked ok in DTS2000 and it works with Excel. Any suggestions?

Thank You

You'll need to set up as many columns as you can have in the data. It's tricky because SSIS doesn't handle variable columns very well without implementing some workarounds. (Read in entire row as one field, later split field up using substrings, or other solutions)

Take a look at this forum using the search feature. This has been discussed quite a bit recently.

|||You may try DataDefractor. It handles variable columns quite nicely.

Regards,
Ivan

Ivan Peev | http://www.cozyroc.com/

importing data

we are trying to import data from a flat file using an uptick (`) as a column separator and {CR/LF} as a record terminator. There is a variable number of columns for each record. The initial record in the flat file has 3 columns. Upon processing this record, the import sets all records to 3 columns and does not read the column separators past the second column (even though there may be up to 7 columns in the record).

This method worked ok in DTS2000 and it works with Excel. Any suggestions?

Thank You

You'll need to set up as many columns as you can have in the data. It's tricky because SSIS doesn't handle variable columns very well without implementing some workarounds. (Read in entire row as one field, later split field up using substrings, or other solutions)

Take a look at this forum using the search feature. This has been discussed quite a bit recently.

|||You may try DataDefractor. It handles variable columns quite nicely.

Regards,
Ivan

Ivan Peev | http://www.cozyroc.com/