Convert .csv to .xls and Import VBA

[Joshua]

Hello all,

I'm trying to automate some reporting we are doing and the first stage in
this is to export data out of a program we use to schedule people. This
export can only be a .csv export but when I'm importing it into my database
and appending that data I'm getting a type conversion error.

However, if I open that .csv file and just save it as an .xls and then
import and append, it works just great. I'm automating this process for
others so I'm trying to make this as simple as possible. Is there a way to
have access programmatically open excel and save as .xls and then I can just
have it import the .xls?

Any help is GREATLY appreciated.

 

[Chris O'C via AccessMonster.com]

It's simpler to change the data type of the field of the table the data is
imported into to the *right* data type than to automate Excel on other users'
pcs.

Chris

 

[Jack Leach]

I would start by trying to find out why the csv import is failing. There
really should be no reason to have to convert this to an xls first.

I've never really done csv imports, but from what I gather there is a spec
file that you can create which will define the fields, datatypes etc for the
import.

If you haven't already, you may want to use the import wizard to get this
set up. After the table is created and the spec is defined you shouldn't
have any problems with auotmating it.

Hopefully this helps some... I'm not quite sure how to go about automating
the conversion, but I don't think you should need to really.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Joshua]

Thanks Jack and Chris. I've actually tried both of these solutions and am
also not sure why they don't work. The date format the .csv is in is
99/99/9999 HH:MM:SS.SSS and so I think that's why it won't let me set the
field to Date/Time format and import.

For some reason I think when you save this as an .xls it automatically
changes that date format to a regular 99/99/9999 HH:MM:SS format. But I can't
change the format that this .csv is exporting so that's my delimma.

I hope that makes sense.

Thanks.

 

[Jack Leach]

I have no idea if this is the "right" way to go about this or not, but it may
work.

Import the csv file into a temp table with that particular date field set as
a string datatype, rather than date. Then run a query or loop that converts
the string into a date, and insert the records into your real table.

I'm not sure if you can retain the 3place seconds precision (I don't know
that access goes that far), but you should be able to use CDate("yourstring")
to convert the imported string from the csv temp table into an actual date
for the real table.

You may have to remove the last three characters before doing this.

It's not very elegant, and probably not quick, but it may work.

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Chris O'C via AccessMonster.com]

The problem is the fractional seconds, making the date the wrong format. Jet
will accept 10:00:00 but not 10:00:00.1. Import into a temp table with the
date/time column as a text field. Use a query with vba to convert the
fractional seconds to a whole number (so 4/11/2009 10:51:07.837 would become
4/11/2009 10:51:08) during an append to the table that needs the imported
data.

Chris

 

[John W. Vinson]

Thanks Jack and Chris. I've actually tried both of these solutions and am
also not sure why they don't work. The date format the .csv is in is
99/99/9999 HH:MM:SS.SSS and so I think that's why it won't let me set the
field to Date/Time format and import.

For some reason I think when you save this as an .xls it automatically
changes that date format to a regular 99/99/9999 HH:MM:SS format. But I can't
change the format that this .csv is exporting so that's my delimma.

Access (unlike Excel) limits date/time fields to the nearest second. A value
with ss.sss will give a datatype conversion error if you try to import it.

You may need to link to, rather than import, the .csv file and use an Append
query to populate your table. You can use the CDate() function to translate a
text string to a date/time.