Import from CSV - Combo Box Column

[cmcfarland]

Hello, Everyone!

I'm working on an Import project where I'll import data from a CSV file and add that data to an existing table, and I just noticed that one of the columns in the destination table is a Combo Box.

I'm not sure how to handle this.

The Combo Box has Last Name, First Name and Client Number set to be chosen from a list when adding data to that column, and I have no idea what the ramifications are when importing data to a column like that.



I can get the data that would make up the package that the combo box format loads in, but I'm not sure if I need to do that or not, or if that would even play into what I need to to.

What do yout think?

Thanks for looking!

[ranman256]

Normally, when you import a file, you import ALL of it, so I dont see the need for a combo box to regulate the import.

The combo would only be good for what kind of file to import.
Your combo must be for something else than the import.

[cmcfarland]

Yes, the combo box is there in the destination table for easier manual input I'm pretty sure.

Seems like my import would be easier if that column was NOT a combo box, but I'm not involved in the makeup of the Access system.

I'm just tasked with importing my data into it.

Do you think I can I perform an import from CSV to a combo box column at all?

Thanks!

[ranman256]

Still not sure what that means. The import process , imports to a table.
Is the combo is a query updated from this table?
or the combo tell what table to put the data during import?

I dont see how FIRST NAME controls import.

[cmcfarland]

OK, sure, that makes sense.

Here's what I see:

I have a table that accounts for expenses for a law firm, and I want to import data from a CSV that lists the Client Number for the transaction along with a column for the number of prints and the tallied cost.

When I open the destination table in Access, if I go to add a row to the table when I get to the column where I'd want to push in the Client Number won't let me just add that number, but rather makes me choose from the combo box list. If I'm importing straight to that table will it ignore the combo box and let me just push in whatever data I want?

[ranman256]

Do you just click a button and the data imports?
or do you have to do this import manually thru the menu, external data , text, import??

Since the client # is in the csv, then i dont see the need to enter the combo box.
I think the combo box is just for doing manual entries.

What is it you need?

[cmcfarland]

Yep, I'm using the External Data,Text import process, and it sounds like I may just be able to ignore the combo box, which is really great!

I'm fishing around now to nail down the mapping of the columns, as when I go into the Advanced menu there's an opportunity to check a box to skip certain fields, and strangely that list seems to contain some things that are column names in the Access table and some that are in the CSV, though it doesn't have all of either and the members of that list seem to change as I work with it. Research that now, as it's probably quite simple when you know what you're doing.

[ranman256]

Using CSV ,in the middle of the import session, click the ADVANCED button and build your SCHEMA/SPEC. map the fields, SAVE the schema.
Then import is a single click of a button...using:
DoCmd.TransferText acImportDelim, SPECname, Table, vFile, True

but you must use the SPECname to the one you built for the csv.

[cmcfarland]

It seems to be the buiding of the Spec that's tripping me up.

Do the column names in the CSV have to exactly match the names and order of the Access table?

[cmcfarland]

Ha! I think I just discovered that I can name my fields in the Advanced screen. I work in MS SQL every day, and I guess it's refreshing to be so out of my element. Humbling for sure, and also not a bad time...

[ssanfu]

I just noticed that one of the columns in the destination table is a Combo Box.

The Combo Box has Last Name, First Name and Client Number set to be chosen from a list when adding data to that column, and I have no idea what the ramifications are when importing data to a column like that.

Just to be clear, you have a look up field in a table?

I never use look up fields.

I have a table that accounts for expenses for a law firm, and I want to import data from a CSV that lists the Client Number for the transaction along with a column for the number of prints and the tallied cost.

The client number for the transaction table is a FK. Therefore, there must be a PK for the client (client number?) in the client table before you can do the import for that client.





Hope this makes sense.....

[cmcfarland]

I does make sense, and while I don't have any say over the way that Access is set up here, I WAS able to execute an import to the table successfully.

It does seem to be that there is a lookup field in the table, though.

Next is to get Outlook to run the task for me...

[cmcfarland]

Hello, and thanks for working with me on this.

Things have gone very well, and I'm very near the end.

I do have something to run by you, though:

You'd mentioned this bit of scripting earlier:

DoCmd.TransferText acImportDelim, SPECname, Table, vFile, True


Can I write that into a batch file to have the process run as a Scheduled Task?

Thanks again for taking a look here!