Change field names (1 Viewer)

[pekajo]

Hi,
I have another issue for you.
I am importing an excel spreadsheet into a table. The issue is that in the first 2 rows of the spreadsheet they have merged some columns and rows so I cannot use them as headings. However in the access table they appear a two rows.
What I need to know is there a easy way of getting the second row of the table, which has the headings, as variables and renaming the fields in the table as the imported table only has F1,F2 etc as fields
Regards
Peter

 

[Micron]

Easy? I'd say not really. You can modify table properties in code, but I wouldn't recommend it and would say that the field names could never be variables as whatever you attempted to put there would simply be the Name property of the field. Not sure what you intend to do with such mixed data either (the field names would be unrelated to the data, no?). Consider importing the table into a staging (temp data) table and then use queries to delete the first n rows if that is a constant, and then other queries to either append or update to a target table. This also has the advantage that imports often mess up the data types, but a target table will accept a string (e.g. 55) as a number into a field of number type.

 

[vhung]

Hi,
I have another issue for you.
I am importing an excel spreadsheet into a table. The issue is that in the first 2 rows of the spreadsheet they have merged some columns and rows so I cannot use them as headings. However in the access table they appear a two rows.
What I need to know is there a easy way of getting the second row of the table, which has the headings, as variables and renaming the fields in the table as the imported table only has F1,F2 etc as fields
Regards
Peter

>always remember that on Accdb Table has specific auto assign field name
if first row is unNamed and auto generated header from Merge-Column on excel
>though you can rename field during conversion excel to Accdb to create table...
see the attachment;

 

[CJ_London]

really depends on how you are importing your excel data. You could use a query along these lines which enables you to change field names and selects from row 3 of the worksheet

SELECT F1 AS myField1, F2 AS myField2
FROM (SELECT * FROM [sheet1$A3:B] AS xlData IN 'C:\pathtofile\nameoffile.xlsx'[Excel 12.0;HDR=No;IMEX=0;ACCDB=Yes]) AS XL;

Once you have got that displaying the data as required, you can change it to an append query.

If you want to use the header values on excel row 2, change A3 to A2 and HDR=Yes

If you want to use the header values on excel row 1, change A3 to A and HDR=Yes then use a criteria ( after ACCDB=Yes]) to exclude row 2 - perhaps something like

WHERE F1<>"code"

Note this last approach does mean your values will all come through as text so you will need to convert to the correct datatype using cDate, cDouble etc as required