Edit tables

[Surreall]

Hi all new to Access and the forums here, so hi to all

Question:

I am not sure how to go about this so i will explain first what i have.

I have 30 tables of 5 columns each. Ultimately i want to create query's based on the first column and to collect info from the other columns. The problem i have is the first column is in a format that is unusable eg "2012.10.10 7:30" (the data is from a csv file, and will be updated regularly so the first column will always be in this format.

I am looking to change the tables (not create new ones, presuming this is the best way of doing it) so a query (or update query) automatically inserts two new columns to each table First column takes the hour, 2nd column gets the minute value. I have no worries in using left and rights to do this.

Then i will be able to create queries using these new columns in the existing tables as a reference.

Any ideas?

Regards

Surreall

PS as these tables will be updated regularly, would it be better to to use VBA or whatever Access uses to import the csv file into access (saving it as the same name as the last) and editing it this way? EG

Code:

For each csv file

Import CSV file
Extract into table form


Insert columns etc (using right and lefts to get the right ones)

Next csv file

[June7]

You don't actually have to modify the tables. Any expressions used in the UPDATE action can just as easily be used in any SELECT query to manipulate the date string into a valid date value or to extract any part from the string. Here is an expression to convert to valid date:
CDate(Replace("1012.10.10 7:30",".","/"))

Why do you have 30 tables? Are the 5 columns identical in all tables? If so, why not one table with another field for Category?

[Surreall]

You don't actually have to modify the tables. Any expressions used in the UPDATE action can just as easily be used in any SELECT query to manipulate the date string into a valid date value or to extract any part from the string. Here is an expression to convert to valid date:
CDate(Replace("1012.10.10 7:30",".","/"))

Why do you have 30 tables? Are the 5 columns identical in all tables? If so, why not one table with another field for Category?

Thank you very much for your help.

The 30 files are more like 3 batches of 10 each. But they will be regularly imported as they are continously changing csv files. Each batch has similar data but the date will not be the same for all as the common column i.e. the date part, some of the batch will have extra dates (not many). And i presume you cannot import two csv files and merge them easily into a single table, when the first column as extra values in some of the csv files? Sorry i am new to access

Rgds

Surreall

EDIT: i presumed the best thing to do was to write vba code that imports all the csv files and then sorts the dates out after wards?

I tried this code to import one, but it failed, could it be cause of the commas and no ""

DoCmd.TransferText acImportDelim, "", "Table1", "C:\Winxp\testing.csv", True, ""

[June7]

Unless argument is required, don't put anything in if you don't have preference. Remove the "" from the arguments. Defaults will be used or argument is ignored when nothing specified. Don't even use the last comma if no argument given. Try:

DoCmd.TransferText acImportDelim, , "Table1", "C:\Winxp\testing.csv", True

[Surreall]

Hiya,

I tried that and got this error:

Run time error 3709
The search key was not found in any record

Rgds

Surreall

[June7]

The procedure works for me. Seems like an odd error for the import code.

Since I can't replicate the error, need to examine your files or an accurate representation. If you can post sample file structures and data or actually attach files, will look at.

[Surreall]

The procedure works for me. Seems like an odd error for the import code.

Since I can't replicate the error, need to examine your files or an accurate representation. If you can post sample file structures and data or actually attach files, will look at.

I got around one problem, change true to false as i have no headers in it. The macro ran fine, but when i went to tables it had produced two. First one saying csv_ImportErrors and the second one, table contents it is empty, but has all the lines.

The csv is set out like this (stock data), when i open in excel. So no "" only ,

Range (A,1): 2011.03.01 14:00,1.01820,1.01888,1.01768,1.01816,3244,0
Range (A,2): 2011.03.01 15:00,1.01820,1.01860,1.01721,1.01780,3363,0

Ranges are to tell you which cell the data is in

Rgds

Surreall

[Surreall]

When the import is working will it be possible to split the data for "space" and "Semi colon" as well as "comma". That way i wouldnt need to split the first column later?

Rgds

Surreall

[June7]

I think that would require custom VBA procedure to accomplish import. What semi-colon - did you mean the colon between hours and minutes? I think you are right to deal with the date data after import.

[Surreall]

I think that would require custom VBA procedure to accomplish import. What semi-colon - did you mean the colon between hours and minutes? I think you are right to deal with the date data after import.

Yes i meant the colon between the hours and minutes.

Any ideas why it is not working yet?

Rgds

Ace

[June7]

I created a csv file of the sample data and imported. The import is successful, even the date values come in as dates (with slashes instead of periods).

I cannot replicate your issue. If you want to provide your files, I will examine.

[Surreall]

Sorry for the late reply, here is my code, do i need to declare anything?

Sub Import_multiple_csv_files()



DoCmd.TransferText acImportDelim, , "Table1", "C:\AUDUSDH1.csv", False



End Sub

[Surreall]

AUDUSDH1.zip

Here is the csv file

Rgds

Surreall

[Surreall]

Could it be i havent downloaded the correct plugins for macros for access, when i first installed?

[June7]

There is no plugin for import, it is part of Access.

There are 7 columns, not 5 in the CSV, but you said 5 fields in the 30 tables.

Tried import your CSV and got 'The search key was not found in any record' error.

Something wrong with your CSV file. I copy/pasted all the lines to a new file and don't get that error. However, import of column 7 does generate error table for the 61 rows with value other than 0. This happens regardless if the table already exists and field is set to Long Integer or Double number.

Interesting that the TransferText code deals with the date value but the import wizard does not. However, neither likes column 7. Sorry, no idea why this happens.