Import CSV, and add field and data according to user entry...?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am setting up a import process, and I need some assistance. I need the
user to be able to import a CSV, but with certain automated processes added.
First, I need a DATE field to be added. Second, I need Access to prompt the
user to enter a date, then enter that information to every record of the
field that was added.

I hope that makes sense. Any suggestions?

Thanks in advance,

Dan
 
Hi,

Are you importing into an existing table OR creating a new table each time?

Will the CSV file be the same name with the same fields or will it be
changed each time?

If the CSV file is the same name with the same fields, you could (1) link to
the CSV file instead of importing it. (2) Use it to append to the existing
table. (3) After appending, run a query like so which will add the date the
user selects:

UPDATE TblJean
SET TblJean.TheDate = [Enter Date]
WHERE (((TblJean.TheDate) Is Null));

If you need to import then create a new Date/Time field, something like this
will work:

ALTER TABLE TblJean
ADD TheDate DATE;
 
Hi Dan,

1) Does the CSV file always have the same structure?

2) Does the data always get appended to the same Access table?

3) How are your VBA skills?

The general idea is

i) create a linked table (always with the same name) connected to the
CSV file

ii) have an append query that transfers the data from the linked table
to the Access table. In the append query, have a calculated field that
gets the date.

iii) run the append query and delete the linked table.

If you can trust the user to enter the date correctly, the append query
can use a simple parameter. Right-click on the title bar of the query
design window and select Parameters, then enter something like

[Enter date for imported data] Date/Time

Then in the query design grid add a field like this:
ImportDate: [Enter date for imported data]
 
Hello again,

I finally got around to testing this, and since my VBA skills are shaky, I
went with the SQL query. However, how would I modify it so one data entry
would change the null values of the records in multiple tables?

For example, the user:

1) Imports the CSVs into the corresponding tables (6 different)
2) Updates all the NULL values in the all the tables to the one date that
he/she enters.

Thanks again for all your help!

Dan

Jerry Whittle said:
Hi,

Are you importing into an existing table OR creating a new table each time?

Will the CSV file be the same name with the same fields or will it be
changed each time?

If the CSV file is the same name with the same fields, you could (1) link to
the CSV file instead of importing it. (2) Use it to append to the existing
table. (3) After appending, run a query like so which will add the date the
user selects:

UPDATE TblJean
SET TblJean.TheDate = [Enter Date]
WHERE (((TblJean.TheDate) Is Null));

If you need to import then create a new Date/Time field, something like this
will work:

ALTER TABLE TblJean
ADD TheDate DATE;


--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


BayAreaDan said:
Hello,

I am setting up a import process, and I need some assistance. I need the
user to be able to import a CSV, but with certain automated processes added.
First, I need a DATE field to be added. Second, I need Access to prompt the
user to enter a date, then enter that information to every record of the
field that was added.

I hope that makes sense. Any suggestions?

Thanks in advance,

Dan
Click to expand...
Click to expand...
 
Back
Top