472,790 Members | 3,536 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,790 software developers and data experts.

Importing a .txt file into an existing database

Hi everybody,

I am totally desperate because I cannot solve a really simple problem:
I have a specific text-file which I want to import into an existing
database. The problem is that the columns have different names so I
receive an error message when importing via the "Get External
Data"-Wizard.
The second problem is that this text file includes several rows
without any relevant data, only symbols like "-----" in the various
columns. I cannot change this data (and I cannot import it directly
via a SQL-statement) because it is extracted from a backend system and
I have no influence on those reports.

Here is an example of my import text file and the target database:
sample text file:
---- ---- ----
C1 C2 C3
---- ---- ----
1234 12A2 00B2
22B2 12A4 00C4

sample target database:
Columns:
Col1 Col2 Col3
1234 12A2 00B2
22B2 12A4 00C4

It really looks so easy, but I don't know how to do it.

THANKS in advance for your support!

Best wishes,
Stephan Kuhnert
Nov 13 '05 #1
2 3857
In message <88**************************@posting.google.com >, kuhni
<st************@gmx.de> writes
Hi everybody,

I am totally desperate because I cannot solve a really simple problem:
I have a specific text-file which I want to import into an existing
database. The problem is that the columns have different names so I
receive an error message when importing via the "Get External
Data"-Wizard.
There are two ways you can get around this.

The first and simplest is to import the data into a new table then
create an append query to add the new table to your existing one. Then
delete the temporary table.

Another way is to use the wizard to import the data into a temporary
file but then save the import specifications and use them later,
specifying the destination table in a docmd transfertxt line.

From one of my databases:

DoCmd.TransferText , "Jobserve Import Specification", "Jobserve", fname,
False
The second problem is that this text file includes several rows
without any relevant data, only symbols like "-----" in the various
columns. I cannot change this data (and I cannot import it directly
via a SQL-statement) because it is extracted from a backend system and
I have no influence on those reports.


This may require pre-processing, possibly in a temporary table as
outlined above. Personally, I would pre-process the text file using a
tool such as awk or PERL before importing it.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #2
st************@gmx.de (kuhni) wrote in message news:<88**************************@posting.google. com>...
Hi everybody,

I am totally desperate because I cannot solve a really simple problem:
I have a specific text-file which I want to import into an existing
database. The problem is that the columns have different names so I
receive an error message when importing via the "Get External
Data"-Wizard.
The second problem is that this text file includes several rows
without any relevant data, only symbols like "-----" in the various
columns. I cannot change this data (and I cannot import it directly
via a SQL-statement) because it is extracted from a backend system and
I have no influence on those reports.

Here is an example of my import text file and the target database:
sample text file:
---- ---- ----
C1 C2 C3
---- ---- ----
1234 12A2 00B2
22B2 12A4 00C4

sample target database:
Columns:
Col1 Col2 Col3
1234 12A2 00B2
22B2 12A4 00C4

It really looks so easy, but I don't know how to do it.

THANKS in advance for your support!

Best wishes,
Stephan Kuhnert


Take a look at

http://www.delphipages.com/result.cfm?ID=4743

with this utility you should be able to import datas in any database.

Tazio.
Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: D Mat | last post by:
Hi, I'm trying to get MS Access 2000 to automatically import a series of (~200) flat text, tab delimited, data files into a single Access table, with consistent fields and rows. The files have...
1
by: sparks | last post by:
I have never done this and wanted to ask people who have what is the best way. One person said import it to excel, then import it into access table. but since this will be done a lot, I am...
3
by: George Yeh | last post by:
Hi There! Currently I am importing a .csv file that eventually grows over time. I have setup my table with an unique id field that is the primary key and indexed. I have also indicated in the...
5
by: dixie | last post by:
If I sent a user an empty database container - dB with no tables and I needed them to import their tables into it and one of their tables was a hidden table with the prefix Usys, is there any way...
2
by: Snozz | last post by:
The short of it: If you needed to import a CSV file of a certain structure on a regular basis(say 32 csv files, each to one a table in 32 databases), what would be your first instinct on how to...
17
by: OdAwG | last post by:
Just some questions regarding tables. I am new Access Database and need a little help. I have the following data listed below 01. I have a table called tbl_Customer with the following...
0
by: Alun Jones | last post by:
I'm getting the above error in a dialog box from Visual Studio 2005 when trying to sign an assembly using a PFX file, and would like to know how to resolve the problem. Background: The PFX...
1
by: puremetal33 | last post by:
I have worked very little with Access and have hit a snag. My task right now is to import the data from a spreadsheet into an existing table in an Access database. I edited the .xls file so that...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.