Change column datatype "$2,000" to 2000

Hello,

How do I change the datatype of a column in a CSV file. Preferably in the select statement (apparently Cast, Convert, & Replace functions don't work when selecting from a CSV).

I have a page where users upload their CSV files to and then I use SQLBulkCopy to insert all the records into my sql table. The problem is with columns of money data.

EX: "$2,000" >> 2000

The CSV file interprets the "$2,000" as a string of text. How do I convert it to a decimal so I can insert it into my sql database?

The CSV doesn't interpret anything, CSV files have no data types, it's all just text. You can use a text editor to do a search and replace and remove all the dollar signs and periods if you wish. You should be able to set the SQL data type for currency as well. Or use a DTS job to change the data format instead of doing a bulk copy.

Jeff

|||

Hi Jeff,

As I said, users are uploading CSV files to my site where I use SQLBulkCopy to automatically upload the records to a SQL table. So that means no manual editing of the csv file and no using DTS.

Or can you do a "DTS job" via code? If so I've never done that before and a code example would be much appreciated.

You're right, I shouldn't have said CSV interprets it. I meant while reading from the CSV to a datatable, etc it is interpreted as string data.

Thanks a bunch,

|||

Moderators: please stop marking this post as resolved/answered. It is obvious Jeff's response is NOT the answer. Marking it so only removes the chance of my question actually being answered.

|||

You can probably trigger a DTS job via code, I've never looked at whether this could be done or how. You may need to parse the CSV file in code as it is uploladed, before the bulk copy, to strip the string portions out and format the currency as a number. I'm not sure if a SQLBulkCopy can change data types, I'm not familiar enough with it to say.

Jeff

PS: This isn't the answer either I'm afraid... :)

CDC not tracking changes

Hi

I'm trying to get CDC going, it works however when i query the LSN using the functions i get no changes. The min and max LSN returns null. SQL agent is running, db is on full recovery model etc.

any ideas ?

thanks

CDC is a SQL Server 2008 feature. Not to mention nothing to do with SSIS. Can you refine your issue so that we can redirect your question appropriately? This is a SQL Server 2005 forum for the SQL Server Integration Services tool.

|||

sorry Phil, but when i search on the forums under "change data capture" - only SSIS forums come up. I'll ask the question internally - i'm in Microsoft. thanks for your troubles.

|||

Sqlgoof wrote:

sorry Phil, but when i search on the forums under "change data capture" - only SSIS forums come up. I'll ask the question internally - i'm in Microsoft. thanks for your troubles.

Try the SQL Server 2008 Data Warehousing forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1617&SiteID=1

Catching return codes of SP in ODBC way

Hi,
I use SQLPrepare and SQLExecute functions to execute a stored
procedure, which may return various codes. I dont know how to catch
these return codes in my VC function.
Is there any API defined in ODBC for retrieving the return code?
Thanks
KarthikI got it from another thread....
If you wanted to access the return value from the stored
procedure, you would execute a command string like the following:
SQLCHAR * szSQLStmt = (SQLCHAR*) "{? = call sp_test(?, ?)}";
And do a binding like this:
SQLINTEGER returnVal;
ret = SQLBindParameter(hstmt1, 1, SQL_PARAM_OUTPUT, SQL_C_SLONG,
SQL_INTEGER, 4, 0,
&returnVal, 0, NULL);
After the call to the stored procedure, returnVal will contain the
return
value.