Import Excel CSV Specific Cells to MS Access Table

[BenMason]

I have a CSV delimited file with row data that I want to transfer to MS Access columns. MS Access is already open; I have a button on Access form to import Excel to the table. For instance, In the CSV file cell b1 and b2 have this data:
JPWestfield
8/1/2019

Customer and date are columns in Access. I want to add excel CSV data to MS Access table as column data:
Customer Date
JPWestfield 8/1/2019

I am looking for VBA Code to do this. I saw this so far, but I think its for Excel and not CSV delimited.
I want to transfer B1 value to Customer and B2 value to Date column in Access. Not sure how to do this. I've found snippets of what I am looking for and so far I have this:
with rs
.AddNew
' add values to each field in the record
.Fields("Customer") = wks.Range("B1").Value
.Fields("Date") = wks.Range("B2").Value
End With
r = r + 1 next row
Loop
rs.Close
db.Close

Since Access is already open not sure if i should open it again. Also I think this may be code for Excel. The vba should be code for CSV delimited file.

2nd option I was thinking is to do a straight transfer dump of CSV to Access into a temp table. Then transfer the data from AccessTempTable to Another Access table field by field into the column format. Not sure how that would work since the data would be in row format in the AccessTempTable.

Your help is appreciated.
Thanks,
Ben

 

[theDBguy]

You may not even need VBA if you can simply import the CSV file into a temporary table and then use an APPEND query to add the data into your actual table.

 

[Gasman]

Well if you are going with the recordset you would need to use r as the row number for the cell range?

 

[BenMason]

Hello the DBguy, I was thinking about that. But the Excel CSV data is in rows. The append in Access inserts into columns. I'm trying to get the row data from Excel into columns in Access. Like:
"JPWestfield" is in row 1 column (B1) in Excel CSV
"8/1/2019" is in row 2 column (B2) Excel CSV

I would like to append it in one(1) row in Access in column like this:

Customer	Date
JPWestfield	8/1/2019

 

[theDBguy]

Hello the DBguy, I was thinking about that. But the Excel CSV data is in rows. The append in Access inserts into columns. I'm trying to get the row data from Excel into columns in Access. Like:
"JPWestfield" is in row 1 column (B1) in Excel CSV
"8/1/2019" is in row 2 column (B2) Excel CSV

I would like to append it in one(1) row in Access in column like this:

Customer	Date
JPWestfield	8/1/2019

Hmm, maybe I'm wrong, but I don't see a problem with that. If I am understanding it correctly, the data in Excel, that you want, probably looks like this:

Column1      Column2
JPWestfield
             8/1/2019

If so, you could perhaps use an APPEND query followed by an UPDATE query to get both data into the table. For example:

INSERT INTO TableName(Column1) SELECT Column1 FROM TempTable
UPDATE TableName SET Column2 = DLookup("Column2", "TempTable", "IsNull(Column2)=False") WHERE Column2 Is Null

(untested)
Just a thought...

 

[Gasman]

I would have thought more like this?

Column1      Column2
            JPWestfield
             8/1/2019

 

[BenMason]

Ah, I see where I need to explain more. Yes, Gasman, the data in ExcelCVS looks like your column 2. I would like to import it into Access so that it looks like
Column1 Column2
JPWestfield 8/1/2019

Ben

 

[theDBguy]

Ah, I see where I need to explain more. Yes, Gasman, the data in ExcelCVS looks like your column 2. I would like to import it into Access so that it looks like
Column1 Column2
JPWestfield 8/1/2019

Ben

Ah, I guess I misunderstood it when you said one data is in column B1 and the other is in column B2.

How many rows are we talking about?

 

[BenMason]

Just one row in Access.

Ben

 

[Pat Hartman]

The append in Access inserts into columns.

NO, the append in a query (not just Access) adds rows. Update, in a query updates columns in existing rows.

If you want to use data in a spreadsheet to update a column in an existing row, you MUST have a unique join field. That would not normally be a name. ALSO, assuming you actually had a valid join field, you would still need to import the spreadsheet into a table and then do the update by joining the permanent table to the temp table you just imported. This of course, is very inefficient and leads to bloat so if this is what you need to do, I would recommend using a separate BE in which you can append the spreadsheet data so you avoid the bloat. We can go into detail if you need it.

 

[bastanu]

You can read the text file (CSV) line by line, many examples on the web, here is one:

VBA - Read Text File Line by Line - Automate Excel

This tutorial will demonstrate how to read content from text files line by line with VBA. We can either read the text file as an entire file, or line by line. Read Text File Line by Line Text in a text file is usually made up of several lines, separated by delimiters. These can be…

www.automateexcel.com

Here is a link to my Google search:

vba import text file line by line - Google Search

www.google.com

Cheers,

 

[Pat Hartman]

Action queries are always better (faster/simpler) than VBA loops to update data. But if you are only updating a few rows, it makes little difference.

 

[ebs17]

the data in ExcelCVS looks like your column 2

Adventurous.
A Comma-separated values file is a text file (plaintext), not an Excel file.
A text table should have equal content in one column, not such a mixture.
Before thinking about it further, it should be shown what else is in the file. In the simple case you show such a CSV file. Knowing the whole thing, there will certainly be a simple and high-performance solution for getting the information of interest into the database table.

 

[CJ_London]

If this is a csv file, forget excel. What is confusing is your description of what you have.

So what does the data look like if you open the .csv file in notepad?

Also, to be clear - what else does the file contain? Other columns? column headers? (post#4 implies not). And are you talking about just 2 rows of data or many rows?