About Geoff Expertise I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.
See my
website for example apps and downloads
Experience I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.
Commercial database design and development work undertaken.
Question Geoff,
First off, thank you for your time!
I have an ACSII (132 column...fixed length) file that I am trying to ulimately put into MySQL tables. But, I have several obstacles:
-File Size is 18 Mb, too big to transfer into MS Excel
-Even though all records are 132 columns, there are many different types of records (different column configurations).
Which means, I need to separate the data into many tables based on record type.
My problem is preparing the data for MySQL. It needs to be delimited...so excel seemed like an easy choice.
Any ideas?
Respectfully,
Aaron Barber
Answer Hello Aaron
If you link the text file directly as a table in Access, you will be lead by the wizard into defining the field breaks and data type definitions. If you choose 'Advanced' during this process, you will be able to store the definition, then use this programatically in VBA with the DoCmd.TransferSpreadsheet command - see onboard halp for the full spec on this.
