csv to table

[manama]

Hi im a complete newbie here.
I hope you can help me...
I am trying to import a csv file into a table... it works fine however it put all of the row in just one column ...
Meaning a use:

DoCmd.TransferText TransferType:=acImportDelim, TableName:="tblTempImport", _
Filename:=CurrentProject.Path & "/xxx.csv", HasFieldNames:=False

and i end up with tblTempImport only having one column F1....
how do i go from her?
Please help )

 

[JohnLee]

Hi,

Can you upload an example or sample of your CSV file, this would help tremendously.

 

[manama]

Apperently im too much girl or blond but i cant upload a csv file? I have added a screendump instead, hope it helps?

 

[gemma-the-husky]

this will be the problem

acImportDelim,

do it manually, and see what happens

either - your file is fixed width, not delimited
or - the file has a different delimiter character to the default, which I think is a tab.

so your system is not seeing your column separators.

there are options to change lots of things while you do the import - which can be saved as a file specification

 

[namliam]

If your file is being loaded into one line, you probably have a UNIX file, which only has a linefeed but no Character return.

You cant mass import these files using transfertext, becuase that only works based on character return. Instead you need to either manually read the file or first add a character return to each line before importing it...
OR ask the creator of the file to fix it for you.

 

[manama]

the first row is : 4;5676010;657411;56671;208;1;;;MI010472;0;163468
just as an example The csv cant be changed, i have to deal with what i have :-/

 

[JohnLee]

Hi,

As you can see from the responses by more experienced Access Guru's your file does not appear to fit the criteria for a csv file.

Are the ";" the data seperator if it is then you can use that as your delimiter.

 

[manama]

I'm sorry the the stupid questions ;( Normally i program in java, this is very different

 

[namliam]

Dont have to change the CSV, but "copy" it to a new file, fix it, then work with it...

Something along these lines:

Public Function CnvFil(InputFile As String, OutputFile As String)    

    Dim Ifn as integer, Ofn as Integer
    Dim Buffer As String
    
    Ifn = FreeFile
    Open InputFile For Input As #Ifn 
    Ofn = FreeFile 
    Open OutputFile For Binary As #Ofn
    Do While Not EOF(Ifn)
                Line Input #Ifn, Buffer  'if UNIX then there will be only one line
                Buffer = Replace(Buffer, vbLf, vbCrLf)
                Put #Ofn , Buffer  ' write file
     Loop
     Close #Ofn
     Close #Ifn
     
 End Function

 

[JohnLee]

Hi,

Based on the example data you provided I have been able to import that data into Access using the SemiColon as the delimiter and the results appear to be fine to me, There are 11 fields as a result.

 

[manama]

namliam i'll try
JohnLee ... here it is just in one column instead of the 11 it is supposed to be :-/

 

[JohnLee]

Hi,

Perhaps I've got this wrong but I thought your wanted the data seperated into as you put it columns [Fields] according to the semicolon seperator, that is what I understood from your example that you provided. So are you wanting all the data to be in one column[Field]?!!

Are you trying to seprate out the data into individual fields [Columns] or are you wanting one complete record per line of data in one field [column]?

 

[manama]

No i want it in columns Right now it all ends up in just one field. I am trying to get it seperated
the result should be a number of rows consisting of 11 columns .... or afterwards i have to add 4 news columns from another csv file, but one thing at the time

 

[JohnLee]

Ok,

I don't know what version of Access your are using but attached is a word document with image illustrations of the process I follow to import csv files. I'm not familiar with the later versions procedure for importing csv files but I am assuming it is something similar to the contects of the attached word document.

As I do not know what naming convension you intend to follow I have stuck with a generic approach, you of course name your table and fields according to your own requirements.

If you follow the images in order in the word document you should hopefully understand the manual import process [which you can then later automat].

First off choose File/Get External Data\Import [1st Image]
Then when the import window pops up navigate to the location of where your text file to be imported is [2nd image], ensure that the "Files of type:" shows "Text Files(*.txt;*.csv;*.tab;*.asc)" and then select the text file to be imported on selecting the required text file click the Import button.

The 3rd image shows the "Import Text Wizard", make sure that the "delimited - Characters such as comma or tab separate each field" radius option is selected, note the sample of your data will appear in the "Sample data from file" window.

Click the "Next" button and the 4th image will appear showing that the "Semicolon" has been identified as the data seperator and you can see that your data is now split into Columns [Fields] accordingly. If the first row contains Field Names ensure that the "First Row Contains Field Names" is checked, otherwise leave alone.

Click the Next button and the next step [the 5th image] will appear, as this is a brand new table ensure that the "In a New Table" radius is selected, then click the "Next" button and you will the be presented with the 6th image.

This is where you will name each of your fields and set the Data type, you will need to do this for each field in turn as shown in the 6th and 7th images. For the purpose of this demonstration I have chosen to set all fields to be of text type data, and used the default given by Access for the field names, however you may wish to keep your numerical fields of "Long integer" type and of course give your fields more meaningful names.

Once you have named your fields and selected the data type click the "Next" button and you will be presented with the 8th image, here you can choose whether or not to have a primary key, and you can choose your own primary key or you can let Access add a primary key for you. It is not a good idea to have no primary key, but that is entirely up to you. once you have decided on your choice, click the "Next" button and you will be presented with the 9th image, here you can decide on the name of your table, Access will use the name of the text file as the default name, as Access does not know what your intentions are in that regard.

Now before you go any further this is the point at which you can now create and save your import specification for future import of data that follow this format, so before you decide on the name of your table Click the "Avanced" button and you will be presented with the 10th image, here you can see all the fields that you named and their data types, click on the "Save As..." button and you will be presented with the 11th image, this is where you can give your import specification a unique name for use later on when you automate your import process.

Again Access uses the text file name as the default but I would recommend that you choose a meaningful name, for the purpose of this demonstration I have chosen "NewTxtDocImportSpec" [The 12th and 13th images] which a shorten version of "New Text Document, Import Specification" and losing any spaces in between. Click the "OK" button and the import specification will be saved within Access and you will be taken to the next image, make sure that you give your new table a meaningful name for your purposes, for the purpose of this demonstration I have chosen "tblNewTxtDoc" and then click the "Finsh" button you will be presented with the next image, which will confirm that your table hase been created using the specification just created.

You can then view your table as shown in the last two images.

I hope this is of assistance to you.

Regards

John

 

[namliam]

while I admire your dedication John, you are using a proper windows file.
The only reason why in windows the whole file will be treated as "one column" is if there is no line delimiter, though technicaly it should still generate MANY columns if it is all one line.

So atleast part of this problem relates back to the seperator, but it resulting in one line can only have one cause beeing no "vbCRLF" but instead only "vbLF"

 

[JohnLee]

Hi NamLiam,

I assumed from the example that manama gave in his/her 4th post that the data was coming from a text file, with semicolon's as delimiters, however having read back through the posts I note that I didn't read the information about it possibly being a unix file and so bombed along with my response. The areas that your talking about are way out of my field of knowledge.

But thanks for the observations.

John