Import data from CSV and export it to CSV again.

[jgaard]

Hi

I do some work for my farther in my past time.

At the moment I'm working on a database, but all the data are spread
out over 5 sections in a CSV file. And even worse It has to be
returned in excactly the same way.

The 5 sections are as follows, # marks the end of a section:

AFSENDER:
#
VAREGRUPPER:
#
VAREPAKNING:
#
VARETYPE:
#
RABATTER:
#
KOMMUNEPRISER:
#

Under each section there several keys, seperated by ;.

Is it possible to import into Access directly?
Is it possible to export back to a CSV file with the same form, if the
file dosn't look excactly like this it can not be validatet by the
customer.

Below I have pasted the CSV file, with 2 imaginary posts.

AFSENDER:;;;;;;;;;;;;;;;;;;;;;
Afsender;EANLOKNR;Senummer;CVRnummer;Forsendelsesdato;Forsendt
af;Email;Versionsnummer;;;;;;;;;;;;;;
Sender info here;;12345678;123456789;17-08-2006;Put my name
here;Email@here;3;;;;;;;;;;;;;;
#;;;;;;;;;;;;;;;;;;;;;
VAREGRUPPER:;;;;;;;;;;;;;;;;;;;;;
Gruppekode;Gruppenavn;Overordnet gruppe;;;;;;;;;;;;;;;;;;;
4711;Industri;;;;;;;;;;;;;;;;;;;;
47111502;Vaskemaskine;4711;;;;;;;;;;;;;;;;;;;
5214;Husholdning;;;;;;;;;;;;;;;;;;;;
52141501;Køleskab;5214;;;;;;;;;;;;;;;;;;;
#;;;;;;;;;;;;;;;;;;;;;
VAREPAKNING:;;;;;;;;;;;;;;;;;;;;;
Varepakkekode;Kolonne1;Kolonne2;Kolonne3;;;;;;;;;;;;;;;;;;
#;;;;;;;;;;;;;;;;;;;;;
VARETYPE:;;;;;;;;;;;;;;;;;;;;;
Varenummer;Varenavn;Betegnelse-for-
nettoindhold;Nettoindhold;Værdibasis;Nettovægt;Miljømærkning;Oprindelsesland;Nøgleord;Billedenavn;Varegruppe;UNSPSC-
kode;Varebeskrivelse;Valuta;Miljøbemærkninger-
producent;Link;Producent;Bestillingsenheds-betegnelse;Mindste-
bestillingsenhed;Sammenlignings-enheds-
betegnelse;Bruttovægt;Varepakkekode
1579658;Product1name;Stk;
1;1;;310;CountryOrigin1;SeriesOfKeywords1;Picturename1.gif;
47111502;47111502;ProductDescriptionDetailed1;DKK;;Link1;MadeBy1;Palle;
1;Stk;;
2485100;Product2name;Stk;
1;1;;;CountryOrigin2;SeriesOfKeywords2;Picturename2.gif;
52141501;52141501;ProductDescriptionDetailed2;DKK;;Link2;MadeBy2;Palle;
1;Stk;;
#;;;;;;;;;;;;;;;; ;;;;;
RABATTER:;;;;;;;;;;;;;;;;;;;;;
Kommunenr;Kodetype;Navn;Intervaltype;Beløbstype;Interval fra;Interval
til;Fradragsstørrelse;;;;;;;;;;;;;;
#;;;;;;;;;;;;;;;;;;;;;
KOMMUNEPRISER:;;;;;;;;;;;;;;;;;;;;;
Kommunenr;Varenummer;Pris;Vare fradrag;Vare tillæg;Ordre fradrag;Ordre
tillæg;Bonusberettiget;Rabatberettiget;Sammenligningspris;;;;;;;;;;;;
123;Product1name;23500;;;;;;;23500;;;;;;;;;;;;
123;Product2name;27600;;;;;;;27600;;;;;;;;;;;;
#;;;;;;;;;;;;;;;;;;;;;

Hope that someone can help me as using Excel for this task i getting a
little harder when the number og VAREGRUPPER is above 30 and the
number of VARETYPE and KOMMUNEPRISER are both very close to 100.

Kind regards J;-)

 

[jgaard]

The following lines should be one line and not multible lines:

Afsender;EANLOKNR;Senummer;CVRnummer;Forsendelsesdato;Forsendt
af;Email;Versionsnummer;;;;;;;;;;;;;;

 

[John Nurick]

There's no simple way of importing this into Access. Here are a couple
of approaches I would consider:

1) pre-process the file by splitting it into several regular CSV
files, one for each section. These can then be imported by Access's
standard import routine. Here's some incomplete VBA:

Sub SplitFileIntoSections(FileName As String)

Dim Sections As Variant
Dim Lines As Variant
Dim SectionText As string
Dim j As Long
Dim OutputName As String

'Split file into sections on #
Sections = Split(FileContents(FileName), "#")

For j = 0 To UBound(Sections)
SectionText = Sections(j)

'delete first line of SectionText if it consists entirely of ###

'now the first word of sectiontext is the section name
'delete first line but remember its first word

'create a filename for the section using the section
'name and original file name
OutputName = blah blah

'create the new file
WriteToFile SectionText, OutputName
Next j 'process the next section
End Sub

The FileContents() and WriteToFile() procedures can be found at
http://www.j.nurick.dial.pipex.com/Code/index.htm


2) pre-process the file by adding the section name as the first field
in each record and deleting the section headers and "#" lines. This
would allow it to be imported into a single Access table, from where
the records could be moved to their final destinations using append
queries.


3) write VBA code to parse the file, identify each record and put it
where it belongs.