Edit tables

[Surreall]

I really appreciate your help thankyou, how do you resave the file? As i am having trouble with that



Rgds

Surreall

[June7]

Resave what file?

[Surreall]

Hi June7,

I have found somewhere else to get my csv files from and now there is no problem with it. They import wonderfully. I have a few questions about how to go about organising them if that is okay?

Regards

Surreall

[Surreall]

Hi again,

I have written the following code, which imports two csv files and renames the fields as follows
Field 1: Date
Field 2: Time

1stTable currently set out like this........(field names in order)......Date, Time, Open AUDUSD60, High AUDUSD60, Low AUDUSD60, Close AUDUSD60, Sales AUDUSD60
2ndTable currently set out like this..........................................Date , Time, Open GBPUSD60, High GBPUSD60, Low GBPUSD60, Close GBPUSD60, Sales GBPUSD60

What i would like to do is write a VBA code that merges these two comparing date & time and add the other columns to that table eg

New merged table would look like Date, Time, Open AUDUSD60, High AUDUSD60, Low AUDUSD60, Close AUDUSD60, Sales AUDUSD60, Open GBPUSD60, High GBPUSD60, Low GBPUSD60, Close GBPUSD60, Sales GBPUSD60

So new table would have 12 columns and where all date and time matched (has to be both)

So if i look at the new table i can go to a particular time and see the sales for both audusd and gbpusd.

The only problem is that gbpusd has about a third of the number of rows as the first table. So there will be blanks next to some of the date and times for the last 5 columns.

Does that make sense?

Rgds

Surreall

PS ultimately i will have a lot more csv files so will look to do this with all of them

Sub Import_multiple_csv_files()


Dim My_Array

My_Array = Array("AUDUSD60", "GBPUSD60")

For i = LBound(My_Array) To UBound(My_Array)

    Currentcsvfile = My_Array(i)

    DoCmd.TransferText acImport, , Currentcsvfile, "C:\" & Currentcsvfile & ".csv", False

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(Currentcsvfile)

    Set fld = tdf.Fields("F1")
    fld.Name = "Date"

    Set fld = tdf.Fields("F2")
    fld.Name = "Time"

    Set fld = tdf.Fields("F3")
    fld.Name = "Open " & Currentcsvfile & ""

    Set fld = tdf.Fields("F4")
    fld.Name = "High " & Currentcsvfile & ""

    Set fld = tdf.Fields("F5")
    fld.Name = "Low " & Currentcsvfile & ""

    Set fld = tdf.Fields("F6")
    fld.Name = "Close " & Currentcsvfile & ""

    Set fld = tdf.Fields("F7")
    fld.Name = "Sales " & Currentcsvfile & ""

    dbs.Close
    Set dbs = Nothing
    Set fld = Nothing
    Set tdf = Nothing

Next i

MsgBox "Changed"
'Call changefieldnames


End Sub

[June7]

Seems to make sense. What exactly do you need? Does code work?

BTW, Date and Time are reserved words in Access/VBA. Should not use reserved words as names.

[Surreall]

Seems to make sense. What exactly do you need? Does code work? BTW, Date and Time are reserved words in Access/VBA. Should not use reserved words as names.

Yep code works great. It loads two tables What i am looking to do i said above, merge the two tables. With Date and time as the reference. Please see above Rgds Surreall

[June7]

Merge? Is the combined Date/Time value unique to each record in each table but is common to both tables? If so, build a query that joins the two tables on the Date/Time fields. Use the query as basis for reports or to make another table.

That or write code to import both csv files to same table.

[Surreall]

Merge? Is the combined Date/Time value unique to each record in each table but is common to both tables? If so, build a query that joins the two tables on the Date/Time fields. Use the query as basis for reports or to make another table. That or write code to import both csv files to same table.

Date and time is the same for both tables, well the second table as some missing times How do import two csv files into the same table? Or more to the point how do i import lost of csv files into the same table? Rgds Surreall

[Surreall]

With vba how do import two csv files into the same table? Yes date and time is the same for both tables. Although the second table has some dates missing Rgds Surreall

[June7]

I don't have need for importing csv files so I could be wrong on methodology, but I don't think could use TransferText to import additional fields to an existing file. TransferText either creates new table or adds records (in the corresponding existing fields) of existing table.

Other method to import csv file would open recordset of the existing table and enter data to specified fields of specific record by looping through the csv file opened as an object in VBA procedure. Code would have to parse each line of the text file, probably to an array using Split function. Then feed each value to recordset. Gets rather tricky. For a start, review
http://forums.aspfree.com/microsoft-...vba-30159.html
http://www.applecore99.com/gen/gen029.asp
http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

[Surreall]

How about using VBA to import the two seperate files have i have done. And then use vba to merge the two tables?

[June7]

Yes, can do.

Do you need code to create the fields - which is what you show in the sample you posted.

Use VBA to execute SQL actions - UPDATE, MAKE TABLE, ALTER TABLE, INSERT - as well as the TableDefs collection for some table edits.

[Surreall]

Help with the code would be great, using my code above. Or a link to get me started? Rgds Surreall

[June7]

Lots of resources. I already posted 3 links.

Here is one about SQL http://w3schools.com/sql/default.asp

[Surreall]

I am having a real problem with syntax for sql for example:

Sub Mergetwotables()

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

Set dbs = CurrentDb

'RunSQL "SELECT AUDUSD60."
'doa.Execute "SELECT AUDUSD60.Date"
'DoCmd.RunSQL "SELECT Date, Time"
'DoCmd "INSERT INTO AUDUSD60 SELECT * FROM GBPUSD60"
dbFailOnError

End Sub

None of these work, i know i am probably doing something stupid, i keep getting debugs on all lines but i don't know the correct syntax for calling sql commands. The two tables are called AUDUSD60 & GBPUSD60

I havent done code for merging yet, as i cannot get the syntax to work with simple statements yet

Regards

Surreall