Trouble with SQL datetime format

[gdutson]

I have written a VI to import a CSV file into a SQL data table but I cannot get it to work when using a table with "datetime" data format.  It will import into a new table with undefined data formats as "varchar" format but no luck when going into a table with the "datetime" data format.   I need the datetime format for date/time queries later. I have attached a zip file of my VI and the CSV file.  Is there a problem with my CSV format or I am I doing something else wrong here? Please help. Thanks, Greg

[sarahk]

Hi, gdutson.

Can you be a little more specific about what happens when you try to use the datetime format? If you receive an error, what is the error number or message? Your CSV file looks good to me, as does your VI, so it might be better to troubleshoot this specifically from the problem itself.

Let me know! Have a nice evening.

[DavidJCrawford]

Hi Greg

I couldn't test your vi specifically as there vi's missing. We might have diffferent versions of the DBT. Anyway I replicated your VI in LV8.0 and DBT 1.0.1 and tested it on a MS Access database. It worked so maybe there is something else going wrong. I even changed the first field format in MS Access to be date/time format and that also worked.

There are few things that come to mind that would be worth checking

1. Is the database open successful?

2. I noticed that your array to cluster is set to 3 elements but your data is only 2 columns. This will only be a problem if you have created the table on you database with only two fields.

You mentioned varchar. Are you working with SQL Server or something else? When I get chance I will try your method on a MSDE Database. Its been a while but hopefully I will be able to try it and get back to you.

Trying to help

David

[DavidJCrawford]

Greg

I have just tried to insert your data into a SQL Server (MSDE) database.

I found that the INSERT data didn't work. The TRUE for Create Table didn't seem to do any thing. So I created a table using labview like this

And this is its design.

I then run the vi again and it starts to work with no issues. So it looks like the Create Table?=TRUE for the INSERT doesn't seem to work on SQL Server. It looks like you need to create the table first.

Here is your data in the database

Let me know what you think

David

Message Edited by David Crawford on 04-28-2006 11:17 AM

[gdutson]

Thanks for your help David,

Based on your feedback I found two (so far) of my programming errors. 

1.  Your right I needed to set my cluster to 2 columns in order to avoid a column size mismatch response from SQL server when I attempted the import.  I had defined my table with 2 columns and was attempting to insert 3 columns based on the cluster size in my VI.

2.  Based on your table design pic, I also changed my colum data type to "float" from "varchar" and that brought the numbers over as numbers versus text.

I also found that in order to get the imported data all the way to DIAdem in numeric format, I also had to change the "format string" option on my Spreadsheet to Array function to a %f to convert the spreadsheet array to an array of numbers versus an attay of text.  Otherwise DIAdem still sees it as txt.

Anyway...thanks again. 

Greg