Importing certain columns from a flat database into Access

[nica12]

Hi,

I am creating a database in Access which will use data imported from flat Excel database - this excel database is very wide with 255+ columns. The source Excel database will be often updated as raw data is run through Power Query for cleaning and the source database will be the PowerQuery output. In importing the Excel data into Access, I ideally want to only import certain columns into each of my individual normalized tables in Access. I would like to do this through linked tables so that the database in Access can be easily updated as the Excel spreadsheet (where the data is pulled from is updated).

The questions I therefore have is: 1) What is the best/simplest way to import just certain columns? Is this possible? Or do I have to split my Excel database into normalised tables first?

2) Once I have imported data through linking - I understand that the tables cannot be amended in Access. Can I still merge or append tables together through primary/foreign keys? Or will I have to import files to be able to do this?



Thanks.

[ranman256]

attach the text file as an external table,
then make an append query to import the columns and data you wish.


text files require a SPEC file to define the columns so there's some pre-steps to take:

---------------
PREP
---------------
1st, manually import the text file,
in the 'import screen' ,bottom left corner is ADVANCED
define your fields in the text as an IMPORT SPEC.
SAVE this spec. (i.e: "SpecName")

manually link this CSV file using the spec name so it becomes an External Table: txtFile2Import

make an append query to append the txtFile2Import fields to the target table: qaImportCsvFile

---------------
EXECUTION
---------------
1. each new CSV file you get, save it to the same place every time,like: c:\temp\txtFile2Import.csv
2. run the import query: docmd.openquery "qaImportCsvFile"
3. Done

[June7]

Linking to a file with 255+ columns won't work, at least not all columns can be pulled by the link. How many of these columns do you want? You are transposing the data in Access to normalized structure? If not, what do you meany by 'normalized'?

It is possible to build a query that links to Excel, CSV or TXT file and specify columns to retrieve. I am just not sure if this can reference columns beyond the 255 limit. Example:

SELECT Seq, Unit, Garage FROM [Units$] IN 'C:\Users\Owner\June\MyStuff\Condos.xlsx'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes];

Modify IMEX=0 and can even edit linked worksheet, although not delete rows.

Could attach sample Excel file for analysis. Follow instructions at bottom of my post.

Is this your cross-post https://www.access-programmers.co.uk...-limit.329246/

[nica12]

Thank you very much for your helpful reply, and sorry I was unclear, I am very new to Access and it's all quite a steep learning curve! That is correct, I am wanting to transpose the data to a normalized structure. For example, I have a referral information table, and I would like to only import certain relevant columns (say, "Referral ID", "Referral date", and "Referral reason") from my large Excel spreadsheet (stored on SharePoint).

Is it possible to build a query which links to the SharePoint site, and specifies columns to retrieve?

[June7]

I have never used SharePoint but apparently the External Data wizard can establish link. However, still have 255 field limit.

No idea if the query method I posted could work.

What you describe is not transposing data, it is just pulling particular fields. Transposing would be converting fields to rows (unpivot) - as described in other forum thread - or rows to fields (pivot).

[CJ_London]

It is possible to build a query that links to Excel, CSV or TXT file and specify columns to retrieve. I am just not sure if this can reference columns beyond the 255 limit

no it’s not possible. Even if you set a range table, it won’t be recognised if it extends beyond the 255th column

[Micron]

I think you would have to use Automation, as long as you always knew (or could derive) the table fields you need out of the 255+ fields.