Import Multiple CSVs with a twist

[Eskimo]

I am trying to import multiple CSV files from a folder.

Each CSV file have all the same structure whereby there is redundant data
that I don't need in rows 1 through 21, Except I need a number that is always
stored in a cell lying on Row 8 and Column 2.

Then the rest of the data I need. Row 22 is all header information, and
anything below that I need to import for analysis.

The twist is that the number from Row 8/Column 2 should be included in the
list imported from row 22 on. ie. for each line imported from row 22 down,
throw in that number on a new column for every line imported.

I hope I make sense there.

Thanks

Jonathan AKA Eskimo

 

[pietlinden]

I am trying to import multiple CSV files from a folder.

Each CSV file have all the same structure whereby there is redundant data
that I don't need in rows 1 through 21, Except I need a number that is always
stored in a cell lying on Row 8 and Column 2.

Then the rest of the data I need.  Row 22 is all header information, and
anything below that I need to import for analysis.

The twist is that the number from Row 8/Column 2 should be included in the
list imported from row 22 on. ie. for each line imported from row 22 down,
throw in that number on a new column for every line imported.

I hope I make sense there.

Thanks

Jonathan AKA Eskimo

This link shows how to transfer only a selected range from Excel into
your DB. You should create an import specification and save it to use
in the Transfer so that you can map the Excel columns to fields in
your table. Then I guess use a function to return the value in
Cell(8,2), and use it in an update query on your dataset after each
import.

 

[Eskimo]

Hi,

What would the function be to import the number on cell 8,2? Its good to
make suggestions on methods to try, but I need detailed instructions rather
than a vague response with a general suggestion.

Thanks,

Jonathan

 

[pietlinden]

Hi,

What would the function be to import the number on cell 8,2? Its good to
make suggestions on methods to try, but I need detailed instructions rather
than a vague response with a general suggestion.

Thanks,

Jonathan

Well, you get what you pay for.

This worked, but was INCREDIBLY slow.

Private Sub Command0_Click()
Me.Text1 = GetXLCellValue("C:\TestJunk\Book1.xls", 8, 2)
End Sub

Private Function GetXLCellValue(ByVal XLFilePath As String, ByVal
intRow As Integer, intcolumn As Integer)
Dim xlApp As Excel.Application


Set xlApp = New Excel.Application
xlApp.Workbooks.Open XLFilePath
GetXLCellValue = xlApp.ActiveSheet.Cells(intRow, intcolumn)

xlApp.Workbooks.Close
xlApp.Quit

Set xlApp = Nothing

End Function

Once you have this, you could write your update query to fill in the
column of values that were blank.

You never mentioned that you needed step by step instructions, so I
gave you a basic outline of what to do. If you need detailed
instructions, you should ask for them. Getting snippy with people
that are helping you will only get you less or no help.

 

[Eskimo]

Hi Pietlinden,

Sorry for the snippy remark, I had a long day.

Perhaps I will re-ask the questions in steps as I realize now that there are
a few steps.

1) Import all CSV's into table(s). one for all or one for each depending on
the next steps. I will need a loop routine for this to import all csv's in
the file. There are constants (ie. filename, number of CSV's in the folder
always 32)

2) Place the number from cell 8, 2 into a column and fill in the column with
the same number for each line after row 22.

3) Other steps which include querying those lines only that have a
"succesful" word in the 6th column but are not blank in the first column.

Thanks again, I will breakdown my steps further rather than have a general
question, that might have contributed to my frustration.

Thanks again.