Export to CSV blank fields with Text Qualifier

[saterry7]

I have two tables identical with all text fields within the same database. One table I export as a csv, delimited with commas separating my fields and quotation text qualifiers. Where the fields are blank there are text qualifiers as well ("" - begining and ending quotations). This is what I need. The second table is exporting as a csv, delimited, commas separating the fields with text qualifier as ""- begining and ending quotations, EXCEPT the blank fields are not putting the text qualifier, and I need it.

First Table: *What I need*
"142358", "PK", "15132678", "", "123.45"

Second Table: *What I need to fix and do NOT want*
"142358", "PK", "15132678", ,"123.45"

Make sense?
Please help!

 

[Uncle Gizmo]

Is there a Null value in the offending Field?

 

[saterry7]

It is empty...there is nothing there...it is null.

 

[Uncle Gizmo]

Can you use Nz() to convert it to "" before it is exported?

 

[vbaInet]

Can you use Nz() to convert it to "" before it is exported?

... you can do that in a query and export the query.

 

[saterry7]

Yeah, I have seen that suggestion on here a few times, the thing that doesn't make sense is it works with the other table just fine. I actually re-built it all (identical again) and it is putting the "" in the null fields except now just one of my fields is not picking up the text qualifier. This field is a number (long integer) field and again....has worked in another database. I am working on combining 2 databases so I have the specifications for everything I need on another database (that works correctly....) Any suggestions?

 

[Uncle Gizmo]

In Excel ... When you copy stuff .... It looks at the first row of data and uses that to define the data type.

So try it with a definite text values in the first row like "TEST", (I know it's not applicable to int or long).

 

[vbaInet]

There's no issue with the routine, it's a data problem.

The first table has the empty string "" in that field whereas in the second table is Null (which it should be). Empty string "" and Null are two different things, and if you look at the Allow Zero-Length property of the field you will find that it's set to Yes.

So:
1. turn off Allow Zero Length
2. do some data cleansing to update all empty strings to Null
3. check all your code/macro to ensure that you're not setting a control's value to "" or vbNullstring

 

[saterry7]

I fixed my problem with the text qualifiers for null fields...now I have just one field that is a number field that I need the text qualifiers around. I feel like because it is a number field it might be causing my problem, but like I said below....I am copying everything from an existing database that I did not originally create and the original has the field as a number at the import and at the make table. When I export from the new database it exports all fields (includinng null) with text qualifiers except this one field.

 

[vbaInet]

You'll definitely be doing more harm than good if you put text qualifiers on a Number field type because you're representing the data type of that field wrongly.

When you begin exporting and importing Access and Excel will determine the data type to use based on the first row and if it sees "" for what is supposed to be a Number field, it will interpret it as Text.

 

[saterry7]

I resolved it. I added CStr in front of my field which was formatted as a number. I know I have done that while I was messing with this but I guess I had too many issues going on that I must have made an error in the process.

 

[saterry7]

I'm not exporting for excel. I'm exporting the csv to import into another system.

 

[vbaInet]

Access and Excel were just examples. I would imagine your application will also want to check which data type to use. It's common practice.

 

[saterry7]

Thanks for everyone's quick help!

 

[vbaInet]

Glad we could help!