[AccessD] converting csv to table

Some ideas:

I assume what you are saying is that the date is stored in your csv table as
yyyymmdd and access is reading it as mmddyyyy (ie american date format) and thus
your dates are screwed up.

Using dim aRecord as you have will default the variable to a variant.  (A side
note, I always explicity declare, that way I avoid unexpected surprises if MS
decides to change the default behaviour)

I don't know what split does, but I am assuming it tries to guess what the
variable type is and then stores it as that type.  I would first check your PC
date format, but I suspect that you will be stuck with US interpretation and
will have to "manually" force the date to be read the correct manner.

First suggestion would be to dim aRecord as a string.  This will force the date
(and other fields) to be stored as they appear in the csv file.  Then you can
split the date field using left$/right$/mid$ and datepart.

If I am incorrectly guessing what your problem is, maybe someelse will have a
better idea.

Stuart

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Oleg_123 at xuppa.com
> Sent: Wednesday, 15 October, 2003 12:10 AM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] converting csv to table
>
>
> Hey Group
> I have to convert a CSV file to Access table, with one of the records
> changing from yearmonthdate(19980123) to
> monthdateyear(01231998) format.
> So I gues I can't use macro for that since it doesn't recognise
> yearmonthdate format. I wrote the code below yesterday and
> tested it at
> home on diff table and it worked up till including Debug.Print
> aRecord(8)line  then I had trouble sending received data to
> table. Now it
> doesn't work at all.. any suggestions ?
>
>
> Dim fs As New Scripting.FileSystemObject
> Dim ts As Scripting.TextStream
> Dim strLine As String
> Dim aRecord
> Dim strSQL As String
> Dim con As New ADODB.Connection
>
> If fs.FileExists("U:\Users\HRIS\oleg\cars testing\trainFROM.csv") Then
>
>     Set ts = fs.OpenTextFile("U:\Users\HRIS\oleg\cars
> testing\trainFROM.csv", ForReading)
>
>     Do Until ts.AtEndOfStream
>
>         strLine = ts.ReadLine
>         aRecord = split(strLine, ",")
>
>         aRecord(8) = Mid(aRecord(8), 5) & Left(aRecord(8), 4)
>
>         Debug.Print aRecord(8)
>
>         strSQL = "INSERT table value(" & aRecord(0) & "," &
> aRecord(8) & ")"
>         'con.Execute (strSQL)
>
>     Loop
> Else
>      MsgBox ("file doesn't exist")
> End If
>
>
>
>
> -----------------------------------------
> Get Breaking News from CNN, ABC, NBC, CBS Now.
> http://www.xuppa.com/news/?link=webmail
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>