Newb Importing data

[qwikmr2]

Access 2010 on Win7 32bit

Ok so I am very new to Access. Good foundations in Excel but this is new.

I have a series of csv text files that my system at work produces. The file does not have headers and as such imports as Field1 etc. I imported the first one I want and changed the field names to reflect the contents. I now need to append more of these files on a daily basis. The import process does not flow because there field names are different. I did a query and was able to add all the field names and then specify the locations to append the new file. This however is very manual and I will have many of these to import. I am sure there is a way to make this happen but am lost. Please help a newby.

Mike

[lfpm062010]

Couple of way to do this without thinking too much. There might be others.

1) Add the header in the Excel file and use the import and append to the existing table.

2) Write a macro/VBA/few queries to do below (as long as the Excel file column data don't change).
a) Import the Excel data to a temporary access table (with table column heading like "Field1", "Field2", ..... etc).
b) Write an append queries using the temp table to import into the main table.

Hope this will help you.

[qwikmr2]

The process I was doing was similar to the second one proposed. The issue I have is that it involved many manual steps to complete. I guess I need to review macro creation a bit more in Access. There has to be an easier way than the method I am using. :-) Thanks

Couple of way to do this without thinking too much. There might be others.

1) Add the header in the Excel file and use the import and append to the existing table.

2) Write a macro/VBA/few queries to do below (as long as the Excel file column data don't change).
a) Import the Excel data to a temporary access table (with table column heading like "Field1", "Field2", ..... etc).
b) Write an append queries using the temp table to import into the main table.

Hope this will help you.

[ItsMe]

...The import process does not flow because there field names are different....

What does this mean?

If the data structure stays the same in the CSV file, use the Wizard to create and save a Named Import Procedure. You can direct the Wizard to your table you already created. Instead of choosing Import Excel Wizard, choose import TEXT FILE Wizard and use the comma separated option.

At the end of the process, save the procedure using a meaningful name. You can then call the Import procedure in VBA using its name and bring the file over as text.

DoCmd.TransferText acImportDelim, "ImportName", "TableName", "FilePathAndName", 0 'Zero indicates that there are not any field names in the file

If there is a snag in the import process, look at your original CSV file. Create a copy, switch the CSV file extension to .TXT, and open it in Notepad. Look for the Commas that separate the values and then identify the qualifiers. The default that the Access Wizard will use will be quotes as qualifiers. Another option for qualifiers is apostrophe. It is a combination of the commas and quotes that create the columns.

[qwikmr2]

That method would likely work perfectly but the files are all named differently. Such as agency02022014.txt, agency02032014.txt
When I save a import process it will only allow me to point to the same file name when I import. Maybe I am doing something wrong with that tool. The files all started off being named the same but its unwieldy to manage if they were stored like that.
Mike

What does this mean?

If the data structure stays the same in the CSV file, use the Wizard to create and save a Named Import Procedure. You can direct the Wizard to your table you already created. Instead of choosing Import Excel Wizard, choose import TEXT FILE Wizard and use the comma separated option.

At the end of the process, save the procedure using a meaningful name. You can then call the Import procedure in VBA using its name and bring the file over as text.

DoCmd.TransferText acImportDelim, "ImportName", "TableName", "FilePathAndName", 0 'Zero indicates that there are not any field names in the file

If there is a snag in the import process, look at your original CSV file. Create a copy, switch the CSV file extension to .TXT, and open it in Notepad. Look for the Commas that separate the values and then identify the qualifiers. The default that the Access Wizard will use will be quotes as qualifiers. Another option for qualifiers is apostrophe. It is a combination of the commas and quotes that create the columns.

[ItsMe]

The saved procedure does not care what the name of the file is. The VBA will tell it the path and name.

DoCmd.TransferText acImportDelim, "ImportName", "TableName", "FilePathAndName", 0 'Zero indicates that there are not any field names in the file

[qwikmr2]

Maybe its the way I am saving the procedure. I am using the External data import wizard. Once I setup everything and import it asks if I want to save the import steps. When I try to run it again it just reloads the same file that I ran when saving. Does that sound right?

[ItsMe]

What name did you use to save the Import process as?

[qwikmr2]

The dialog box says Save Import Steps Save as: name of import Description and then a save import button. Once I save it and reopen it simply points back to the same file I used when importing.

[ItsMe]

What name did you use to save the Import process as?

[qwikmr2]

I just saved it as Patdata import. When I click saved imports it is there as I named. The file always points to the same one that was imported during the save. I even tried editing the path and it errors stating no data found in that location etc. I wish it would just prompt for the file I want to run with the saved settings.

[ItsMe]

As mentioned in post #2, you will likely need to employ VBA. You said you want to automate this and not have to click so many buttons as you interact with Access. My proposal is to use VBA to call your saved Import procedure. Now that you have it saved and you have a name for it, you can move to implementing a VBA procedure.

You will need to identify an event that will fire the VBA code you write. I suggest you start with a new, blank form. PLace a single Command Button on the form. Create a Click Event Procedure to handle the event. The event is the user clicking the button on your new form.

The following code uses the name of your saved Import Process.

DoCmd.TransferText acImportDelim, "Patdata import", "TableName", "FilePathAndName", 0 'Zero indicates that there are not any field names in the file

You will have to adjust the VBA to match other arguments within the statement. For instance the path and name. The following example uses a folder named "test" on the C drive.

DoCmd.TransferText acImportDelim, "Patdata import", "TableName", "C:\Test\MyFile.csv", 0 'Zero indicates that there are not any field names in the file

You will also have to adjust the code to match the name of the table that you are importing to.

DoCmd.TransferText acImportDelim, "Patdata import", "TableName", "C:\Test\MyFile.csv", 0 'Zero indicates that there are not any field names in the file