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

re: Importing a .txt file into an existing database

In message <88afb50f.0408020400.39f6d7f0@posting.google.com >, kuhni
<stephankuhnert@gmx.de> writes[color=blue]
>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.[/color]

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
[color=blue]
>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.[/color]

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.

re: Importing a .txt file into an existing database

stephankuhnert@gmx.de (kuhni) wrote in message news:<88afb50f.0408020400.39f6d7f0@posting.google. com>...[color=blue]
> 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[/color]

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.