Importing a csv file

[itechC]

Hi all!

I'm trying to import a csv file that is comma dilimited. My csv file has a time column which some rows go over 24 hours. Now if i import the file using the import wizard i get no problems. When i try to automate it using the docmd, the rows that have the time over 24 do not get imported. The ones it does import are formated with "AM" at the end. My time field in my table is setup as a text field. Does anyone have any ideas why its doing that.

 

[traingamer]

Try using a specification in your command:

DoCmd.TransferText acImportDelim, [b]Yourspecification[/b], strTblName, strCsv, True

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[itechC]

I tried creating a schema.ini file. It give me a error saying the text file specification does not exist.??? Is there a different way to use a specification?

vTableName = "tblTermCodeOld"
vPath = "C:\Dialer rpt\Term Code 111806.csv"
vspec = "C:\Dialer rpt\schema.ini"

DoCmd.TransferText acImportDelim, vspec, vTableName, vPath, 1

 

[traingamer]

File->Get External Data -> Import
Select your csv file manually, then choose 'Advanced' , set up the spec and save it.

Then use that specification for your TransferText command.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[itechC]

I tried that and it still doesn't work. The file i import from the wizard give me the correct time 69:01:00 but the file i import using docmd and using "myspec" that i saved while importing does not include any time over 24.

 

[itechC]

DoCmd.TransferText acImportDelim, myspec, vTableName, vPath, True

It isn't taking my spec?? Even if i change the spec to a spec that doesn't exist for example:

DoCmd.TransferText acImportDelim, myspec99, vTableName, vPath, True

It will still import the data the exact same way. Am i not referencing my specification correctly?

 

[PHV]

DoCmd.TransferText acImportDelim, [!]"[/!]myspec[!]"[/!], vTableName, vPath, True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Do a query on the new table, with this for the TIME column...

Select CDbl([YourTimeColumn])
From...

If you have VALUES greater than ONE, those correspond to TIMES greater than ONE DAY (24 hrs)

I suspect that you do. However, ACCESS does not DISPLAY times greater than one day. I suspect that you are "seeing" the fractional part only, but that the integral part IS there as well.



Skip,

_[red][/red]

 

[SkipVought]

The other possibility is to CONVERT the TIME column to DOUBLE.

Another is to parse the HOURS, MINUTES and SECONDS each to a separate column and calculate accordingly in use.

Skip,

_[red][/red]

 

[PHV]

Skip, reread the OP's first post:

My time field in my table is setup as a text field

So, I guess he should use the spec to force access to import as a text field instead of a DateTime field.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Why would anyone want date/time values as TEXT except in a report or output file?


Did you not specify TYPE during IMPORT?

Skip,

_[red][/red]

 

[PHV]

Skip, how do you convert "25:12:34" in Datetime format with DoCmd.TransferText ?

 

[SkipVought]

With a wand and incantation?

Skip,

_[red][/red]

 

[itechC]

Thanks everyone. Putting myspec in "" worked. I'm actually parsing the date time field and then converting the seconds to integer. Thanks again.