Import data (transfer) from excel and exclude some coloumns/rows

[metad]

Hi. I know how to import data from an excel file to a table. But my problem is to exclude some rows. I have tried following, but it does not work:

DoCmd.TransferSpreadsheet acImport, 8, "FlightInfo", "C:\Avgang.xls", False, "A, B, D, G, J, L"

It works, if I write "A:L", but I do not want C, E, F etc.

Can anybody help?

Thanks in advance.
metad

 

[ghudson]

Check the Access help files on how to create an import specification to do what you want.

 

[metad]

I did not found anything in the help file!!

I have already done that, but nothing worth there.

Have you any tips?

Thanks
metad

 

[Surjer]

Already Discussed

Topic less than a week ago SEARCH

 

[metad]

I havealraedy seen this, but...

this will not solve my problem because the information stored in some rows/colomns are not good (Text as number/Number as text i Excel) and I want to exclude this rows/columns. If I make a temporary table to import everything to this table, I will still get errors and problems with this rows/columns. Another reason to exclude this before importing is that I imort 2 Excel files to the same table. The first excel file has 15 columns ad the second one has 14. Therefore I get errors because the files have not the same sum of columns.

I think the best way for me would be to exclude the columns I do not want when I import the file to the access table.

I hope you understand more now, and that you you can help me to solve this problem.

Thanks in advance.
metad

 

[Surjer]

Hmmm - Well, I dont think you can do exactly what you are trying to do. I hate that you can't define the fields when you are importing excel files. Any way you can save the excel files as *.csv files? Ahh - thats too much work. I would seriously create a temp table and just define the fields you have problems with as a text format. That way either a number or text can go in there. (Your gonna disreguard that field anyway right?) And then just query over the right fields to your good table.

HTH

 

[metad]

Difference number of columns...Can I deletecolumns before importing?

Yes. I can do the way you are suggesting, but the problem is following:

- How to hide messages the system gives when importing (key violations)?
- How to store both excel files in the same table according to difference number of columns. File 1 has 13 columns and file 2 has 14 fields?

Perhaps I could handle this mater with deleting columns before importing? If so, then I could delete the “problem columns” and the extra column in the file 2 before importing?

Do you know how I can delete columns by code in the excel files before importing?

Than you in advance!

 

[Surjer]

'Disable the warnings
DoCmd.SetWarnings False

'Create a new Recordset
Dim rs As ADODB.Recordset
       Set rs = New ADODB.Recordset
'build the recordset fields
        With rs
            'add some fields
            .Fields.Append "NumberField", ADODB.DataTypeEnum.adDouble
            .Fields.Append "StringField", ADODB.DataTypeEnum.adBSTR, 255
            .Fields.Append "DateField", ADODB.DataTypeEnum.adDate
            
             'Delete some fields
            .Fields.Delete (0)
            .Fields.Delete (1)
            .Fields.Delete (2)

            'open the recorset for editing, you prob dont need this part
            .CursorType = ADODB.CursorTypeEnum.adOpenStatic
            .LockType = ADODB.LockTypeEnum.adLockOptimistic
            .Open
        End With
        
DoCmd.SetWarnings True
End Sub