changing format of fields when importing

[geert.van.ransbeeck]

Hello

The code below imports a csv-file into a table.
For the field F18 I don't get what I want.
In the csv-file 'F18' is of a 'general' format. I don'g get the data
correctly, it has been transformed into a 'time format'. Even if I add
the ALTER TABLE ALTER COLUMN statement to change the data into numbers
doesn't help. Now I have some blancs where there even shouldn't be
one.

Can somebody help?

DoCmd.DeleteObject acTable, "TBL_Import_TPXP_Radi_College_Newcsvfile"
DoCmd.TransferText acImportDelim, ,
"TBL_Import_TPXP_Radi_College_Newcsvfile", "X:\TRANSIT60\AFTP
\USPB1815\RADICOL\NEW.CSV", False, ""

DoCmd.RunSQL "ALTER TABLE [TBL_Import_TPXP_Radi_College_Newcsvfile]
ALTER COLUMN [F18] number"

 

[Dale Fye]

Don't delete the destination table. Create it once, modify the field
datatypes as appropriate.

Then delete all the records.

Now use the GetExternalData wizard to import the CSV, but when you do this,
click on the "Advanced" button in the lower left corner of the wizard to open
the Import Specification Wizard. It will allow you to individually identify
the datatype for each field in your csv file, as well as indicating which
fields to skip, if you want to do this.

Once you have done this, click the "SaveAs" button, and name the import
specification. From then on, when you use the TransferText method on a file
with this particular format, use this ImportSpecification.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[geert.van.ransbeeck]

I still don't get it.
When I import it manually with the GetExternalData wizard it works.
But then when I do it by the TransferText -mode it doesn't work.
As proposed by Dale I saved the 'import specification' as
'Import_New_CSV' and mentioned it with the statement below.

DoCmd.DeleteObject acTable, "TBL_Import_TPXP_Radi_College_Newcsvfile"
DoCmd.TransferText acImportDelim, Import_New_CSV,
"TBL_Import_TPXP_Radi_College_Newcsvfile", "X:\TRANSIT60\AFTP
\USPB1815\RADICOL\NEW.CSV", False, ""


DoCmd.RunSQL "ALTER TABLE [TBL_Import_TPXP_Radi_College_Newcsvfile]
ALTER COLUMN [F1] text"
DoCmd.RunSQL "ALTER TABLE [TBL_Import_TPXP_Radi_College_Newcsvfile]
ALTER COLUMN [F18] double"
DoCmd.RunSQL "UPDATE TBL_Import_TPXP_Radi_College_Newcsvfile SET
TBL_Import_TPXP_Radi_College_Newcsvfile.F1 = Right('000000000000' &
[TBL_Import_TPXP_Radi_College_Newcsvfile]![F1],12);"


Can anybody help? Thanks a lot!

 

[Dale Fye]

You didn't do what I told you to do.

1. Get rid of the line that reads:

Docmd.DeleteObject acTable, "tbl_import_TPXP_Radi_College_Newcsvfile"

Then, go into that table in design view and set the appropriate field data
types.

If you must, add a line of code that deletes all of the values from that
table:

currentdb.execute "DELETE * FROM tbl_import_TPXP_Radi_College_Newcsvfile"

2. When you created the import specification, did you select the various
fields (1 and 18) and set their data types to the appropriate settings? If
not, do that as well

3. Now run the TransferText command, importing the data into the saved table

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.