SPSSX Discussion

Trying to save comma-quote file

Classic

List

Threaded

15 messages Options

Lynda B Kane

Trying to save comma-quote file

I need to save my data in a comma-quote format where all text fields are surrounded by quotes. I was able to successfully use the script Export sav file to free format.SBS from Raynald's website for the smallest of the files (around 6,000 records), but it won't run for the other two files (one has about 128,000 records and the other has 330,000 records). All the files have the same format with 69 variables. Anyone have an idea what could be going wrong?

I've also tried using WRITE OUTFILE but I can't have trailing spaces within the quotes (one value might be "Score" while another might be "Raw Score") which I still seemed to have even when using NOTABLE.

I also tried to just put the quotes in the data and then save as a .CSV but that caused the fields with the quotes to have triple-quotes (i.e. """Score""" rather than "Score"). Which I though was pretty weird. When I tried without putting the quotes in the fields, saving as CSV only put quotes around the fields when they contained a comma - I MUST have quotes around all text files to meet the requirements of the spec (I tried submitting data without them and it failed it's load).

Any assistance with this would be greatly appreciated! Oh, and I'm currently using SPSS 15.0 but I do have SPSS 16.0 available as well.

Lynda B. Kane
SchoolNet Data Analyst
Department of Assessment, Research, and Evaluation
Cleveland Metropolitan School District
E-mail: [hidden email]

"The primary goal of the Cleveland Metropolitan School District is to become a premier school district in the United States of America."

"The primary goal of the Cleveland Metropolitan School District is to become a premier school district in the United States."
====================To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

Maguin, Eugene

Re: Trying to save comma-quote file

Lynda,

>>I need to save my data in a comma-quote format where all text fields are
surrounded by quotes. I was able to successfully use the script Export sav
file to free format.SBS from Raynald's website for the smallest of the files
(around 6,000 records), but it won't run for the other two files (one has
about 128,000 records and the other has 330,000 records). All the files
have the same format with 69 variables. Anyone have an idea what could be
going wrong?

Well, no. But, when it didn't run for the other two files, did you get an
error message(s) or warning(s)? If so, please post that/those to the list.
If there were no errors/warnings, how do you know it didn't run correctly?
I'd be that several persons on the list have done what you want to do, but
you've got to help us with details of what you did.

I'd like to propose that your other methods to solve the problem be put on
hold until the method that worked for the one file is debugged for the other
two files and either fixed or discarded.

>>I've also tried using WRITE OUTFILE but I can't have trailing spaces
within the quotes (one value might be "Score" while another might be "Raw
Score") which I still seemed to have even when using NOTABLE.

>>I also tried to just put the quotes in the data and then save as a .CSV
but that caused the fields with the quotes to have triple-quotes (i.e.
"""Score""" rather than "Score"). Which I though was pretty weird. When I
tried without putting the quotes in the fields, saving as CSV only put
quotes around the fields when they contained a comma - I MUST have quotes
around all text files to meet the requirements of the spec (I tried
submitting data without them and it failed it's load).

Gene Maguin

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

Bruce Weaver

Re: Trying to save comma-quote file

Administrator

In reply to this post by Lynda B Kane

Lynda B Kane wrote

I need to save my data in a comma-quote format where all text fields are surrounded by quotes. I was able to successfully use the script Export sav file to free format.SBS from Raynald's website for the smallest of the files (around 6,000 records), but it won't run for the other two files (one has about 128,000 records and the other has 330,000 records). All the files have the same format with 69 variables. Anyone have an idea what could be going wrong? I've also tried using WRITE OUTFILE but I can't have trailing spaces within the quotes (one value might be "Score" while another might be "Raw Score") which I still seemed to have even when using NOTABLE. I also tried to just put the quotes in the data and then save as a .CSV but that caused the fields with the quotes to have triple-quotes (i.e. """Score""" rather than "Score"). Which I though was pretty weird. When I tried without putting the quotes in the fields, saving as CSV only put quotes around the fields when they contained a comma - I MUST have quotes around all text files to meet the requirements of the spec (I tried submitting data without them and it failed it's load). Any assistance with this would be greatly appreciated! Oh, and I'm currently using SPSS 15.0 but I do have SPSS 16.0 available as well. Lynda B. Kane SchoolNet Data Analyst Department of Assessment, Research, and Evaluation Cleveland Metropolitan School District E-mail: lynda.b.kane@cmsdnet.net "The primary goal of the Cleveland Metropolitan School District is to become a premier school district in the United States of America." "The primary goal of the Cleveland Metropolitan School District is to become a premier school district in the United States."

If this is a one-time job, and you don't mind having some manual steps in the middle of it, use SAVE TRANSLATE to save the data in Excel files. Then open them with Excel, and Save-As .CSV files.

--
Bruce Weaver
bweaver@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

PLEASE NOTE THE FOLLOWING:
1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above.
2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/).

Lynda B Kane

Re: Trying to save comma-quote file

Bruce,

Thank you for your reply. Unfortunately Excel does the same thing as SPSS and only puts quotes around text fields that contain commas - I need quotes around all the text fields. And it's not a one-time job - I'll have about 5 files per year that have to be processed with this set up plus another 10-20 files with an additional layout that will have the same issue.

Lynda B. Kane
SchoolNet Data Analyst
Department of Assessment, Research, and Evaluation
Cleveland Metropolitan School District
E-mail: [hidden email]

"The primary goal of the Cleveland Metropolitan School District is to become a premier school district in the United States of America."

-----"SPSSX(r) Discussion" <[hidden email]> wrote: -----

To: [hidden email]
From: Bruce Weaver <[hidden email]>
Sent by: "SPSSX(r) Discussion" <[hidden email]>
Date: 08/21/2009 10:24AM
Subject: Re: Trying to save comma-quote file

Lynda B Kane wrote:

>
> I need to save my data in a comma-quote format where all text fields are
> surrounded by quotes.  I was able to successfully use the script
> Export sav file to free format.SBS from Raynald's website for the smallest
> of the files (around 6,000 records), but it won't run for the other two
> files (one has about 128,000 records and the other has 330,000
> records).  All the files have the same format with 69
> variables.  Anyone have an idea what could be going wrong?  
> I've also tried using WRITE OUTFILE but I can't have trailing spaces
> within the quotes (one value might be "Score" while another might be
> "Raw Score") which I still seemed to have even when using NOTABLE.
>   I also tried to just put the quotes in the data and then save as a
> .CSV but that caused the fields with the quotes to have triple-quotes
> (i.e. """Score""" rather than "Score").  Which I though was pretty
> weird.  When I tried without putting the quotes in the fields, saving
> as CSV only put quotes around the fields when they contained a comma - I
> MUST have quotes around all text files to meet the requirements of the
> spec (I tried submitting data without them and it failed it's load).
>   Any assistance with this would be greatly appreciated!  Oh,
> and I'm currently using SPSS 15.0 but I do have SPSS 16.0 available as
> well.   Lynda B. Kane  SchoolNet Data Analyst  Department
> of Assessment, Research, and Evaluation Cleveland Metropolitan School
> District E-mail:   [hidden email]   "The primary
> goal of the Cleveland Metropolitan School District is to become a premier
> school district in the United States of America." "The primary goal
> of the Cleveland Metropolitan School District is to become a premier
> school district in the United States."
>

If this is a one-time job, and you don't mind having some manual steps in
the middle of it, use SAVE TRANSLATE to save the data in Excel files. Then
open them with Excel, and Save-As .CSV files.

-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/
"When all else fails, RTFM."

NOTE: My hotmail address is for posting only, and messages sent to it will
be deleted.

--
View this message in context: http://www.nabble.com/Trying-to-save-comma-quote-file-tp25079812p25080785.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

Lynda B Kane

Re: Trying to save comma-quote file

In reply to this post by Maguin, Eugene

I don't get any error messages or warnings when I run the script (.SBS) file. I know it didn't work because it isn't running for long (when it's successful it takes quite a while to run & I can't run anything else on my machine or see the original data file in the data editor) and the output file (.TXT) that I'm creating has a size (1K) but is empty (when it works for the 6,000 record file, it's about 3.5MB when it's done and I can see the records in it when opened in PFE or Notepad).

I'd love to try to step through it to see if I can find exactly where it fails but I don't know how to do that in the SPSS Script Editor.

Lynda B. Kane
SchoolNet Data Analyst
Department of Assessment, Research, and Evaluation
Cleveland Metropolitan School District
E-mail: [hidden email]

"The primary goal of the Cleveland Metropolitan School District is to become a premier school district in the United States of America."

-----"SPSSX(r) Discussion" <[hidden email]> wrote: -----

To: [hidden email]
From: Gene Maguin <[hidden email]>
Sent by: "SPSSX(r) Discussion" <[hidden email]>
Date: 08/21/2009 10:02AM
Subject: Re: Trying to save comma-quote file

Lynda,

>>I need to save my data in a comma-quote format where all text fields are
surrounded by quotes. I was able to successfully use the script Export sav
file to free format.SBS from Raynald's website for the smallest of the files
(around 6,000 records), but it won't run for the other two files (one has
about 128,000 records and the other has 330,000 records). All the files
have the same format with 69 variables. Anyone have an idea what could be
going wrong?

Well, no. But, when it didn't run for the other two files, did you get an
error message(s) or warning(s)? If so, please post that/those to the list.
If there were no errors/warnings, how do you know it didn't run correctly?
I'd be that several persons on the list have done what you want to do, but
you've got to help us with details of what you did.

I'd like to propose that your other methods to solve the problem be put on
hold until the method that worked for the one file is debugged for the other
two files and either fixed or discarded.

>>I've also tried using WRITE OUTFILE but I can't have trailing spaces
within the quotes (one value might be "Score" while another might be "Raw
Score") which I still seemed to have even when using NOTABLE.

>>I also tried to just put the quotes in the data and then save as a .CSV
but that caused the fields with the quotes to have triple-quotes (i.e.
"""Score""" rather than "Score"). Which I though was pretty weird. When I
tried without putting the quotes in the fields, saving as CSV only put
quotes around the fields when they contained a comma - I MUST have quotes
around all text files to meet the requirements of the spec (I tried
submitting data without them and it failed it's load).

Gene Maguin

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

Maguin, Eugene

Re: Trying to save comma-quote file

Lynda,

I saw that .sbs in your original message, didn't know what it meant and
assumed you had made a typo. I don't know anything about scripts. So I can't
offer any help there. I think there are others on the list who are very
skillful with them.

I saw the reply from Bruce and your reply. I don't use excel so I don't know
whether what I am about to suggest has the same problems. Could you either
export the file directly to access through ODBC or could you write out a
comma delimited file (without the quotes) and then import that file into
access. Once in access, you could export the file at a generic text file.
But, here is the relevant part. The default text file export structure in
access is comma delimited with quoted text fields. I agree it's a pretty
clumsy to do it and I haven't tried it but I think it would work.

Gene Maguin

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

Lynda B Kane

Re: Trying to save comma-quote file

Using ODBC to go directly to Access and then Exporting from Access to a text file works, however it takes forever to get the file into Access via ODBC (over 30 minutes for the 300,000 record file). I'd really like to try to get this to work without having to jump between software products if possible. If it's not, then that's a solution I can live with.

Lynda B. Kane
SchoolNet Data Analyst
Department of Assessment, Research, and Evaluation
Cleveland Metropolitan School District
E-mail: [hidden email]

"The primary goal of the Cleveland Metropolitan School District is to become a premier school district in the United States of America."

-----"SPSSX(r) Discussion" <[hidden email]> wrote: -----

To: [hidden email]
From: Gene Maguin <[hidden email]>
Sent by: "SPSSX(r) Discussion" <[hidden email]>
Date: 08/21/2009 11:48AM
Subject: Re: Trying to save comma-quote file

Lynda,

I saw that .sbs in your original message, didn't know what it meant and
assumed you had made a typo. I don't know anything about scripts. So I can't
offer any help there. I think there are others on the list who are very
skillful with them.

I saw the reply from Bruce and your reply. I don't use excel so I don't know
whether what I am about to suggest has the same problems. Could you either
export the file directly to access through ODBC or could you write out a
comma delimited file (without the quotes) and then import that file into
access. Once in access, you could export the file at a generic text file.
But, here is the relevant part. The default text file export structure in
access is comma delimited with quoted text fields. I agree it's a pretty
clumsy to do it and I haven't tried it but I think it would work.

Gene Maguin

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

Peck, Jon

Re: Trying to save comma-quote file

Why don’t you use the WRITE command?

-Jon Peck

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Lynda B Kane
Sent: Friday, August 21, 2009 10:22 AM
To: [hidden email]
Subject: Re: [SPSSX-L] Trying to save comma-quote file

Lynda B. Kane
SchoolNet Data Analyst
Department of Assessment, Research, and Evaluation
Cleveland Metropolitan School District
E-mail: [hidden email]

Lynda B Kane

Re: Trying to save comma-quote file

Jon,

Thanks for your response. I tried the WRITE command with NOTABLE but I was still getting trailing spaces within the quote string and that was also found to be not acceptable by the load program that gets the files after I format them. I always thought NOTABLE was more like Free Form but apparently it isn't.

Lynda B. Kane
SchoolNet Data Analyst
Department of Assessment, Research, and Evaluation
Cleveland Metropolitan School District
E-mail: [hidden email]

"The primary goal of the Cleveland Metropolitan School District is to become a premier school district in the United States of America."

-----"SPSSX(r) Discussion" <[hidden email]> wrote: -----

To: [hidden email]
From: "Peck, Jon" <[hidden email]>
Sent by: "SPSSX(r) Discussion" <[hidden email]>
Date: 08/21/2009 01:06PM
Subject: Re: Trying to save comma-quote file

Why don’t you use the WRITE command?

-Jon Peck

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Lynda B Kane
Sent: Friday, August 21, 2009 10:22 AM
To: [hidden email]
Subject: Re: [SPSSX-L] Trying to save comma-quote file

Using ODBC to go directly to Access and then Exporting from Access to a text file works, however it takes forever to get the file into Access via ODBC (over 30 minutes for the 300,000 record file). I'd really like to try to get this to work without having to jump between software products if possible. If it's not, then that's a solution I can live with.
Lynda B. Kane
SchoolNet Data Analyst
Department of Assessment, Research, and Evaluation
Cleveland Metropolitan School District
E-mail: [hidden email]

Maguin, Eugene

Re: Trying to save comma-quote file

Lynda,

A clarification question with respect to Jon's suggestion. Are you saying
that you might have a string variable such as xxa defined as a10 and xxa
might contain, for example,

Q234567890
Wert973
Kj11
e33

And that the loading program wants those data to appear as

"Q234567890"
"Wert973"
"Kj11"
"e33"

And not as

"Q234567890"
"Wert973 "
"Kj11 "
"e33 "

I'd like to come back to my proposal, clumsy though it is. I think you can
skip the ODBC business which seems to be very slow. Here's a little sample
read-write operation

data list / v1 to v9 1-18 s1 19-28(a) s2 29-33(a) s3 34-38(a)
s4 39-43(a) s5 44-48(a).
begin data
112131415161718191qwertyuiopasdfghjkl;zxcvbnm<>/
212131415161718191qwertyuiopasdfghjkl;zxcvbnm<>/
312131415161718191qwertyuiopasdfghjkl;zxcvbnm<>/
412131415161718191qwertyuiopasdfghjkl;zxcvbnm<>/
512131415161718191qwertyuiopasdfghjkl;zxcvbnm<>/
612131415161718191qwertyuiopasdfghjkl;zxcvbnm<>/
712131415161718191qwertyuiopasdfghjkl;zxcvbnm<>/
812131415161718191qwertyuiopasdfghjkl;zxcvbnm<>/
912131415161718191qwertyuiopasdfghjkl;zxcvbnm<>/
end data.

save translate outfile='c:\temp\lyndatest.txt'/type=csv/
textoptions delimiter=",".

If you create a new access database and define one table having one field,
you can then import the file you created, in this case
'c:\temp\lyndatest.txt'. Access will create a new table with default field
names and and define field types. How exactly it defines field types and
widths, especially for string fields I do not know and like spss and the get
data command, which used to read only the first so many cases to determine
field width for string variables, access may also have a little issue here
also.

Gene Maguin

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

djhurio

Re: Trying to save comma-quote file

In reply to this post by Lynda B Kane

Lynda B Kane wrote

I need to save my data in a comma-quote format where all text fields are surrounded by quotes. I was able to successfully use the script Export sav file to free format.SBS from Raynald's website for the smallest of the files (around 6,000 records), but it won't run for the other two files (one has about 128,000 records and the other has 330,000 records). All the files have the same format with 69 variables. Anyone have an idea what could be going wrong? I've also tried using WRITE OUTFILE but I can't have trailing spaces within the quotes (one value might be "Score" while another might be "Raw Score") which I still seemed to have even when using NOTABLE. I also tried to just put the quotes in the data and then save as a .CSV but that caused the fields with the quotes to have triple-quotes (i.e. """Score""" rather than "Score"). Which I though was pretty weird. When I tried without putting the quotes in the fields, saving as CSV only put quotes around the fields when they contained a comma - I MUST have quotes around all text files to meet the requirements of the spec (I tried submitting data without them and it failed it's load). Any assistance with this would be greatly appreciated! Oh, and I'm currently using SPSS 15.0 but I do have SPSS 16.0 available as well. Lynda B. Kane SchoolNet Data Analyst Department of Assessment, Research, and Evaluation Cleveland Metropolitan School District E-mail: lynda.b.kane@cmsdnet.net "The primary goal of the Cleveland Metropolitan School District is to become a premier school district in the United States of America." "The primary goal of the Cleveland Metropolitan School District is to become a premier school district in the United States."

====================To manage your subscription to SPSSX-L, send a message to
LISTSERV@LISTSERV.UGA.EDU (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

--
http://djhurio.wordpress.com/
http://twitter.com/djhurio
http://www.linkedin.com/in/martinsliberts
http://www.csb.gov.lv/

Rick Oliver-3

Re: Trying to save comma-quote file

This doesn't solve your problem, but SPSS does what it does when saving in CSV format because it is a fairly standard convention for CSV files. For example, Excel does the same thing.

You could use the CONCAT and RTRIM functions to add quotes, as in:

concat stringvar=concat('"', rtrim(stringvar), '"').

Stick that inside a DO REPEAT to modify multiple string variables.

Alternatively, you could write a Python job that wouldn't even require that you know the variable names. (And maybe one of the existing Python modules already does that. I'm sure Jon Peck would know.)

From:	djhurio <[hidden email]>
To:	[hidden email]
Date:	07/30/2010 07:48 AM
Subject:	Re: Trying to save comma-quote file
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

Hi, Do we have a solution to this problem: "When I tried without putting the quotes in the fields, saving as CSV only put quotes around the fields when they contained a comma - I MUST have quotes around all text files to meet the requirements of the spec (I tried submitting data without them and it failed it's load)." It is so strange - from SPSS Syntax Guide: "QUALIFIER=“char”. Specifies the qualifier to use to enclose values that contain the delimiter character." Why SPSS is trying to redefine the format?!? It is so common to have ALL string variables in quotes when saved as TXT. And I do not see the point not to quote all string variables. Is it a way to reduce the file size? Lynda B Kane wrote: > > I need to save my data in a comma-quote format where all text fields are > surrounded by quotes.  I was able to successfully use the script > Export sav file to free format.SBS from Raynald's website for the smallest > of the files (around 6,000 records), but it won't run for the other two > files (one has about 128,000 records and the other has 330,000 > records).  All the files have the same format with 69 > variables.  Anyone have an idea what could be going wrong?   > I've also tried using WRITE OUTFILE but I can't have trailing spaces > within the quotes (one value might be "Score" while another might be > "Raw Score") which I still seemed to have even when using NOTABLE. >   I also tried to just put the quotes in the data and then save as a > .CSV but that caused the fields with the quotes to have triple-quotes > (i.e. """Score""" rather than "Score").  Which I though was pretty > weird.  When I tried without putting the quotes in the fields, saving > as CSV only put quotes around the fields when they contained a comma - I > MUST have quotes around all text files to meet the requirements of the > spec (I tried submitting data without them and it failed it's load). >   Any assistance with this would be greatly appreciated!  Oh, > and I'm currently using SPSS 15.0 but I do have SPSS 16.0 available as > well.   Lynda B. Kane  SchoolNet Data Analyst  Department > of Assessment, Research, and Evaluation Cleveland Metropolitan School > District E-mail:   [hidden email]   "The primary > goal of the Cleveland Metropolitan School District is to become a premier > school district in the United States of America." "The primary goal > of the Cleveland Metropolitan School District is to become a premier > school district in the United States." > > ====================To manage your subscription to SPSSX-L, send a message > to > [hidden email] (not to SPSSX-L), with no body text except the > command. To leave the list, send the command > SIGNOFF SPSSX-L > For a list of commands to manage subscriptions, send the command > INFO REFCARD > > ----- --http://djhurio.wordpress.com/http://twitter.com/djhuriohttp://www.linkedin.com/in/martinslibertshttp://www.csb.gov.lv/-- View this message in context:http://spssx-discussion.1045642.n5.nabble.com/Trying-to-save-comma-quote-file-tp1089451p2259495.htmlSent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

djhurio

Re: Trying to save comma-quote file

Actually you are right. And I was wrong.

I always thought that strings have to be quoted in CSV. Apparently not:
http://en.wikipedia.org/wiki/Comma-separated_values

OK thanks for a suggestion, but I will better tweak my R code to read CSV files correctly :)

Regards,
Martins

On 30 July 2010 16:30, Rick Oliver [via SPSSX Discussion] <[hidden email]> wrote:

This doesn't solve your problem, but SPSS does what it does when saving in CSV format because it is a fairly standard convention for CSV files. For example, Excel does the same thing.

You could use the CONCAT and RTRIM functions to add quotes, as in:

concat stringvar=concat('"', rtrim(stringvar), '"').

Stick that inside a DO REPEAT to modify multiple string variables.

Alternatively, you could write a Python job that wouldn't even require that you know the variable names. (And maybe one of the existing Python modules already does that. I'm sure Jon Peck would know.)

From: djhurio <[hidden email]>
To: [hidden email]
Date:
07/30/2010 07:48 AM
Subject:
Re: Trying to save comma-quote file
Sent by: "SPSSX(r) Discussion" <[hidden email]>

Hi, Do we have a solution to this problem: "When I tried without putting the quotes in the fields, saving as CSV only put quotes around the fields when they contained a comma - I MUST have quotes around all text files to meet the requirements of the spec (I tried submitting data without them and it failed it's load)." It is so strange - from SPSS Syntax Guide: "QUALIFIER=“char”. Specifies the qualifier to use to enclose values that contain the delimiter character." Why SPSS is trying to redefine the format?!? It is so common to have ALL string variables in quotes when saved as TXT. And I do not see the point not to quote all string variables. Is it a way to reduce the file size? Lynda B Kane wrote: > > I need to save my data in a comma-quote format where all text fields are > surrounded by quotes.  I was able to successfully use the script > Export sav file to free format.SBS from Raynald's website for the smallest > of the files (around 6,000 records), but it won't run for the other two > files (one has about 128,000 records and the other has 330,000 > records).  All the files have the same format with 69 > variables.  Anyone have an idea what could be going wrong?   > I've also tried using WRITE OUTFILE but I can't have trailing spaces > within the quotes (one value might be "Score" while another might be > "Raw Score") which I still seemed to have even when using NOTABLE. >   I also tried to just put the quotes in the data and then save as a > .CSV but that caused the fields with the quotes to have triple-quotes > (i.e. """Score""" rather than "Score").  Which I though was pretty > weird.  When I tried without putting the quotes in the fields, saving > as CSV only put quotes around the fields when they contained a comma - I > MUST have quotes around all text files to meet the requirements of the > spec (I tried submitting data without them and it failed it's load). >   Any assistance with this would be greatly appreciated!  Oh, > and I'm currently using SPSS 15.0 but I do have SPSS 16.0 available as > well.   Lynda B. Kane  SchoolNet Data Analyst  Department > of Assessment, Research, and Evaluation Cleveland Metropolitan School > District E-mail:   [hidden email]   "The primary > goal of the Cleveland Metropolitan School District is to become a premier > school district in the United States of America." "The primary goal > of the Cleveland Metropolitan School District is to become a premier > school district in the United States." > > ====================To manage your subscription to SPSSX-L, send a message > to > [hidden email] (not to SPSSX-L), with no body text except the > command. To leave the list, send the command > SIGNOFF SPSSX-L > For a list of commands to manage subscriptions, send the command > INFO REFCARD > > ----- --
http://djhurio.wordpress.com/http://twitter.com/djhuriohttp://www.linkedin.com/in/martinslibertshttp://www.csb.gov.lv/-- View this message in context:http://spssx-discussion.1045642.n5.nabble.com/Trying-to-save-comma-quote-file-tp1089451p2259495.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

View message @ http://spssx-discussion.1045642.n5.nabble.com/Trying-to-save-comma-quote-file-tp1089451p2259527.html
To unsubscribe from Re: Trying to save comma-quote file, click here.

--
Mārtiņš Liberts
+371 26 411 543 (mobile)
+371 67 366 618 (office)
http://djhurio.wordpress.com/
http://twitter.com/djhurio

Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

--
http://djhurio.wordpress.com/
http://twitter.com/djhurio
http://www.linkedin.com/in/martinsliberts
http://www.csb.gov.lv/

Jon K Peck

Re: Trying to save comma-quote file

In reply to this post by Rick Oliver-3

In fact, CSV format is quite variable in general. There are many flavors and no definitive standard.
In fact, Python has a CSV module that could be used here. Here is quote from the documentation for it:

There is no “CSV standard”, so the format is operationally defined by the many applications which read and write it. The lack of a standard means that subtle differences often exist in the data produced and consumed by different applications. These differences can make it annoying to process CSV files from multiple sources.

Jon Peck
SPSS, an IBM Company
[hidden email]
312-651-3435

From:	Rick Oliver/Chicago/IBM@IBMUS
To:	[hidden email]
Date:	07/30/2010 07:35 AM
Subject:	Re: [SPSSX-L] Trying to save comma-quote file
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

From:	djhurio <[hidden email]>
To:	[hidden email]
Date:	07/30/2010 07:48 AM
Subject:	Re: Trying to save comma-quote file
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

Hi, Do we have a solution to this problem: "When I tried without putting the quotes in the fields, saving as CSV only put quotes around the fields when they contained a comma - I MUST have quotes around all text files to meet the requirements of the spec (I tried submitting data without them and it failed it's load)." It is so strange - from SPSS Syntax Guide: "QUALIFIER=“char”. Specifies the qualifier to use to enclose values that contain the delimiter character." Why SPSS is trying to redefine the format?!? It is so common to have ALL string variables in quotes when saved as TXT. And I do not see the point not to quote all string variables. Is it a way to reduce the file size? Lynda B Kane wrote: > > I need to save my data in a comma-quote format where all text fields are > surrounded by quotes.  I was able to successfully use the script > Export sav file to free format.SBS from Raynald's website for the smallest > of the files (around 6,000 records), but it won't run for the other two > files (one has about 128,000 records and the other has 330,000 > records).  All the files have the same format with 69 > variables.  Anyone have an idea what could be going wrong?   > I've also tried using WRITE OUTFILE but I can't have trailing spaces > within the quotes (one value might be "Score" while another might be > "Raw Score") which I still seemed to have even when using NOTABLE. >   I also tried to just put the quotes in the data and then save as a > .CSV but that caused the fields with the quotes to have triple-quotes > (i.e. """Score""" rather than "Score").  Which I though was pretty > weird.  When I tried without putting the quotes in the fields, saving > as CSV only put quotes around the fields when they contained a comma - I > MUST have quotes around all text files to meet the requirements of the > spec (I tried submitting data without them and it failed it's load). >   Any assistance with this would be greatly appreciated!  Oh, > and I'm currently using SPSS 15.0 but I do have SPSS 16.0 available as > well.   Lynda B. Kane  SchoolNet Data Analyst  Department > of Assessment, Research, and Evaluation Cleveland Metropolitan School > District E-mail:   [hidden email]   "The primary > goal of the Cleveland Metropolitan School District is to become a premier > school district in the United States of America." "The primary goal > of the Cleveland Metropolitan School District is to become a premier > school district in the United States." > > ====================To manage your subscription to SPSSX-L, send a message > to > [hidden email] (not to SPSSX-L), with no body text except the > command. To leave the list, send the command > SIGNOFF SPSSX-L > For a list of commands to manage subscriptions, send the command > INFO REFCARD > > ----- --
http://djhurio.wordpress.com/
http://twitter.com/djhurio
http://www.linkedin.com/in/martinsliberts
http://www.csb.gov.lv/-- View this message in context:http://spssx-discussion.1045642.n5.nabble.com/Trying-to-save-comma-quote-file-tp1089451p2259495.htmlSent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

Rick Oliver-3

Re: Trying to save comma-quote file

That's why I included the qualifier "fairly". ;-)

From:	Jon K Peck/Chicago/IBM@IBMUS
To:	[hidden email]
Date:	07/30/2010 08:48 AM
Subject:	Re: Trying to save comma-quote file
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

From:	Rick Oliver/Chicago/IBM@IBMUS
To:	[hidden email]
Date:	07/30/2010 07:35 AM
Subject:	Re: [SPSSX-L] Trying to save comma-quote file
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

From:	djhurio <[hidden email]>
To:	[hidden email]
Date:	07/30/2010 07:48 AM
Subject:	Re: Trying to save comma-quote file
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

Hi, Do we have a solution to this problem: "When I tried without putting the quotes in the fields, saving as CSV only put quotes around the fields when they contained a comma - I MUST have quotes around all text files to meet the requirements of the spec (I tried submitting data without them and it failed it's load)." It is so strange - from SPSS Syntax Guide: "QUALIFIER=“char”. Specifies the qualifier to use to enclose values that contain the delimiter character." Why SPSS is trying to redefine the format?!? It is so common to have ALL string variables in quotes when saved as TXT. And I do not see the point not to quote all string variables. Is it a way to reduce the file size? Lynda B Kane wrote: > > I need to save my data in a comma-quote format where all text fields are > surrounded by quotes.  I was able to successfully use the script > Export sav file to free format.SBS from Raynald's website for the smallest > of the files (around 6,000 records), but it won't run for the other two > files (one has about 128,000 records and the other has 330,000 > records).  All the files have the same format with 69 > variables.  Anyone have an idea what could be going wrong?   > I've also tried using WRITE OUTFILE but I can't have trailing spaces > within the quotes (one value might be "Score" while another might be > "Raw Score") which I still seemed to have even when using NOTABLE. >   I also tried to just put the quotes in the data and then save as a > .CSV but that caused the fields with the quotes to have triple-quotes > (i.e. """Score""" rather than "Score").  Which I though was pretty > weird.  When I tried without putting the quotes in the fields, saving > as CSV only put quotes around the fields when they contained a comma - I > MUST have quotes around all text files to meet the requirements of the > spec (I tried submitting data without them and it failed it's load). >   Any assistance with this would be greatly appreciated!  Oh, > and I'm currently using SPSS 15.0 but I do have SPSS 16.0 available as > well.   Lynda B. Kane  SchoolNet Data Analyst  Department > of Assessment, Research, and Evaluation Cleveland Metropolitan School > District E-mail:   [hidden email]   "The primary > goal of the Cleveland Metropolitan School District is to become a premier > school district in the United States of America." "The primary goal > of the Cleveland Metropolitan School District is to become a premier > school district in the United States." > > ====================To manage your subscription to SPSSX-L, send a message > to > [hidden email] (not to SPSSX-L), with no body text except the > command. To leave the list, send the command > SIGNOFF SPSSX-L > For a list of commands to manage subscriptions, send the command > INFO REFCARD > > ----- --
http://djhurio.wordpress.com/
http://twitter.com/djhurio
http://www.linkedin.com/in/martinsliberts
http://www.csb.gov.lv/-- View this message in context:http://spssx-discussion.1045642.n5.nabble.com/Trying-to-save-comma-quote-file-tp1089451p2259495.htmlSent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD