Import Excel (CSV) to Access (your opinion)

[calvinle]

Hi guys,

I just have a question for most of you that have experience on it.

I have a data file in CSV.
I have used in the past excel macro to extract that CSV file to meet my requirement which is to keep wanted and delete unwanted rows from that data file in excel.

However, I notice that it's better that I use Access to manipulate some field of that data file, etc.. now I want to transfer all that work into an Access Database.

My question:
1/ Should I do all the extracting data in Excel, then import the result in Access?
2/ Should I import the whole raw content of csv, then use Access VBA to extract all wanted records only?

How would you do it?

Thanks

 

[pbaldy]

Personally I'd do it all in Access, but I'm more comfortable with that object model. It's fairly common to import raw data into a local "temp" table, then manipulate it as desired before importing into the "real" table(s). Sounds like that might be a way for you to go.

 

[gemma-the-husky]

access and excel can be awkward working together. you do not get control over certain aspects of the import. It is far better to import a csv directly into access. I also prefer not to touch "raw" files, so that the import process can be standardised and repeated.

note also that by importing a csv into excel, and then saving it, the content of the csv can change anyway.

 

[The_Doc_Man]

The only problem anyone ever reports consistently for this kind of thing is an inconsistent data type assignment. That is, column 6 on one particular file might be imported as though it were a general number, but the same column on a different file that is nonetheless supposed to be in the same format might come back as text.

As far as the other part of this, I frequently import from Excel to a temporary table, then use queries to delete rows that I don't want, then finally do an INSERT INTO from the temp table to the final table. That part works OK.

 

[calvinle]

When you guys export data from 1 table to another, do you use For Each field or you bring each field 1 by 1 by it's name?

 

[The_Doc_Man]

The fields in the source can be whatever Excel chooses to name them. Look at the INSERT INTO syntax and realize that you can name the destination fields AND the source fields if you use the

INSERT INTO xyz (FName, MName, LName, ExtraTitle, ...) SELECT Field1, Field2, Field3 ... FROM importedspreadsheettable ;

One INSERT INTO. You would do several DELETE * FROM importedspreadsheettable WHERE (Not IsEmpty(Field1), Not IsNull( Field2 ), etc. etc.

 

[arnelgp]

you can use direct file method for excel and text files:

Select B.* From [Excel 12.0 Xml;IMEX=2;HDR=YES;ACCDB=YES;Database=C:\yourWorkBook.xlsx].[yourworkSheet$] As B

select * from [Text;FMT=Delimited(,);HDR=Yes;IMEX=2;;ACCDB=YES;DATABASE=thePathofCSVfile].[yourCSVFile#csv]

 

[gemma-the-husky]

The only problem anyone ever reports consistently for this kind of thing is an inconsistent data type assignment. That is, column 6 on one particular file might be imported as though it were a general number, but the same column on a different file that is nonetheless supposed to be in the same format might come back as text.

As far as the other part of this, I frequently import from Excel to a temporary table, then use queries to delete rows that I don't want, then finally do an INSERT INTO from the temp table to the final table. That part works OK.

This is the reason I hate importing excel. You don't get this with a csv or txt file, because you can declare an import spec, and force the column types to be whatever you want.

with Access/Excel, the routine is all automated. It works out what it thinks the column type is based on the first few rows ... So if the column is text, but the first few are numbers, you get a number column, and a big "import errors table". If the column is real numbers, but the first few are integers, you will get errors there as well. You can force a column to be text, by adding a row 2, and putting some text in there - so that the column is forced to import as text - and then deleting the row from the import table.

For these reasons, if you don't have to work with excel, then don't. As simple as that, I think.

 

[calvinle]

I have export all the data from excel to my tables tblTemp to tblData, so now, I will need to complete the clean up.

There are duplicates data from my tblData that I want to delete them. However, they are not in ascending order. I Wonder if I can sort them in by field, then remove all duplicate, then resort them by PK?

Here is my code for removing the duplicate:

 Set rs1 = CurrentDb.OpenRecordset("tblData", dbOpenDynaset)
  
  If rs1.BOF And rs1.EOF Then
    MsgBox "No records to process"
  Else
    rs1.MoveFirst
    Do Until rs1.EOF
      strDupName = rs1![MEMBER_ID]
      If strDupName = strSaveName Then
        rs1.Delete
      Else
        strSaveName = rs1![MEMBER_ID]
      End If
      rs1.MoveNext
    Loop
  End If
  
  rs1.Close
  Set rs1 = Nothing