Delimited text not importing correctly!! This is weird

[memaxt]

Hi all

I have a csv file with one row containing delimited text via a comma

Example

1, James, Smith, Manchester, email, telephone, notes etc..

Think there are 50 comma separations all together.

Anyway when I go to import / link my csv into access the data that is on the first row should it create individual fields where a comma has been placed... But it has doesnt quite worked, some of the fields are created and the rest have been put on a separate row! Rather than going to a new field.

Rather having 50 fields I've got 21 fields and 3 rows or delimited text... HELP

 

[plog]

Sounds like you have a newline character (http://en.wikipedia.org/wiki/Newline) in there. What's it look like in a text editor (Notepad)? Does it break into a new record at some point?

Can you send the file to me?

 

[memaxt]

Please find attached CSV file

Apologies there now commas there semi-colons i've used...anyway

It contains 4 fields, 3 of which are non delimited and the 4th field contains lots delimited data, As you will notice, I have wrote ;this is a test;this is a test;this is a test between the semi-colons,



but when importsonme of the data goes onto seperate rows.

 

[plog]

It contains 4 fields, 3 of which are non delimited and the 4th field contains lots delimited data,

That makes no sense. Being delimited is how fields are defined in the file. Delmited is like being pregnant, there's no sort of or partially; it either is or it isn't. Further, it can't be delimited by one character for a certain section then another character for another section.

If your file is delmited by commas, I get 1 row of data and 5 fields:

Field1 = 13
Field2 = 1061
Field3 = 1061
Field4 = Null
Field5 = long text

If I say its delmited by semi-colons I get 1 row with one field.

I really think your file is improperly formatted. What should the data be in that file? Give me row count, field count and the values for the fields.

 

[memaxt]

I found the problem. There was 2 NewLine Characters within the text which was the equals sign "="

Once all "=" were removed all appeared to import correctly.

Many thankss for your help!!

 

[memaxt]

new problem with delimited text.

I have a field where the user can free text, if they use "=" or "(" ")" or use any other other characters it will cause a new row when importing delimited text.

How can i stop this, so all delimited text is on ONE row.

I have my .CSV files linked to my database

 

[plog]

when importing delimited text...my .CSV files linked to my database

Again, that doesn't make sense: It's either/or. Either you import your data into a table, or you link it, not both. If you truly are importing there is an option in the wizard to allow you to select a Text Qualifier. That dropdown is here you would select " as the character that does so.

Who controls this .csv file? Why are using it for data input? Why not just make a form in Access for them to use which would allow you to validate their data and make importing that file irrelevant?

 

[memaxt]

Apologies.

I have linked the .csv file via the wizard (where I can see the data doing to multiple rows.)

Basically, I receive data in an email like this: (Outlook linked to Database)

#Name#Tel#DOB#POSTCODE#EMAIL#ETC#

I then export it to and .csv file

then LINK it back to the Database where it is then put in to seperate columns. (via the wizard) and appended to a table.


I know its long winded but i'm still struggling converting the delimited to columns within access itself, thats why i revert to the proccess above.

 

[vbaInet]

I suppose you should upload a db with the linked table and the text file and we'll have a look.

 

[gemma-the-husky]

#Name#Tel#DOB#POSTCODE#EMAIL#ETC#

if you get a file structured in this way, then import/or link to it, asserting the hash char as the separator.

I would try not to mess with the file in excel.

note that a row in a table has an absolute limit of 4000 chars (all fields, excluding memos). A lot of long strings can exceed the limit.

 

[memaxt]

I fixed it! Yay

It was due to users adding carriage returns in the memo fields.

I added a function to take care of it before the table is exported.