Unloading Data to .csv Format
Pam asked how to export tables from an IDS 10.0.FC4 database to CSV. Answers: set DBDELIMITER=, (or use UNLOAD TO file DELIMITER ",") with UNLOAD TO ... SELECT *, and consider external tables for large volumes; embedded commas are backslash-escaped rather than quoted. Jonathan Leffler cautioned that this is not true CSV and suggested his SQLCMD (from IIUG), which quotes string fields and doubles internal quotes, plus the old csv2unl/unl2csv Perl scripts. The poster said she'd check what the customer accepts; no final outcome is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
All, I am being asked if there is a way to export the contents of tables in an IDS10.0.FC4 database to a .csv format file. Does anyone know if this is available? Thanks. Pam
Sure.
export DBDELIMITER=,
from SQL:
unload to <yourfile> select * from <table>
A simplistic answer, if you have a lot of data, you should consider external
tables, but you can specify a delimiter for that as well.
cheers
j.
> On Feb 23, 2018, at 3:44 PM, Ekstrand, Pamela A. -ND
<Pamela.A.Ekstrand.-ND@disney.com> wrote:
>
> All,
>
> I am being asked if there is a way to export the contents of tables in an
> IDS10.0.FC4 database to a .csv format file. Does anyone know if this is
> available?
>
> Thanks.
>
> Pam
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Right, I was just concerned about the possibility of commas imbedded within
the data.
________________________________________
From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of Jack Parker
[jack.parker4@verizon.net]
Sent: Friday, February 23, 2018 3:51 PM
To: ids@iiug.org
Subject: Re: Unloading Data to .csv Format [40758]
Sure.
export DBDELIMITER=,
from SQL:
unload to <yourfile> select * from <table>
A simplistic answer, if you have a lot of data, you should consider external
tables, but you can specify a delimiter for that as well.
cheers
j.
> On Feb 23, 2018, at 3:44 PM, Ekstrand, Pamela A. -ND
<Pamela.A.Ekstrand.-ND@disney.com> wrote:
>
> All,
>
> I am being asked if there is a way to export the contents of tables in an
> IDS10.0.FC4 database to a .csv format file. Does anyone know if this is
> available?
>
> Thanks.
>
> Pam
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
The commas will get =E2=80=9Cescaped=E2=80=9D. Like this:
ABC\\\\,DEF\\\\,GHJ,
cheers
j.
> On Feb 23, 2018, at 3:55 PM, Ekstrand, Pamela A. -ND =
<Pamela.A.Ekstrand.-ND@disney.com> wrote:
>=20
> Right, I was just concerned about the possibility of commas imbedded =
within=20
> the data.=20
>=20
> ________________________________________=20
> From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of Jack =
Parker=20
> [jack.parker4@verizon.net]=20
> Sent: Friday, February 23, 2018 3:51 PM=20
> To: ids@iiug.org=20
> Subject: Re: Unloading Data to .csv Format [40758]=20
>=20
> Sure.=20
>=20
> export DBDELIMITER=3D,=20
>=20
> from SQL:=20
> unload to <yourfile> select * from <table>=20>=20
> A simplistic answer, if you have a lot of data, you should consider =
external=20
> tables, but you can specify a delimiter for that as well.=20
>=20
> cheers=20
> j.=20
>=20
>> On Feb 23, 2018, at 3:44 PM, Ekstrand, Pamela A. -ND=20
> <Pamela.A.Ekstrand.-ND@disney.com> wrote:=20
>>=20
>> All,=20
>>=20
>> I am being asked if there is a way to export the contents of tables =
in an=20
>> IDS10.0.FC4 database to a .csv format file. Does anyone know if this =
is=20
>> available?=20
>>=20
>> Thanks.=20
>>=20
>> Pam=20
>>=20
>>=20
>>=20
>=20
> =
**************************************************************************=
*****=20
>> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>>=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
Ah, ok, thanks much.
________________________________________
From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of Jack Parker
[jack.parker4@verizon.net]
Sent: Friday, February 23, 2018 3:59 PM
To: ids@iiug.org
Subject: Re: Unloading Data to .csv Format [40760]
The commas will get =E2=80=9Cescaped=E2=80=9D. Like this:
ABC\\\\,DEF\\\\,GHJ,
cheers
j.
> On Feb 23, 2018, at 3:55 PM, Ekstrand, Pamela A. -ND =
<Pamela.A.Ekstrand.-ND@disney.com> wrote:
>=20
> Right, I was just concerned about the possibility of commas imbedded =
within=20
> the data.=20
>=20
> ________________________________________=20
> From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of Jack =
Parker=20
> [jack.parker4@verizon.net]=20
> Sent: Friday, February 23, 2018 3:51 PM=20
> To: ids@iiug.org=20
> Subject: Re: Unloading Data to .csv Format [40758]=20
>=20
> Sure.=20
>=20
> export DBDELIMITER=3D,=20
>=20
> from SQL:=20
> unload to <yourfile> select * from <table>=20>=20
> A simplistic answer, if you have a lot of data, you should consider =
external=20
> tables, but you can specify a delimiter for that as well.=20
>=20
> cheers=20
> j.=20
>=20
>> On Feb 23, 2018, at 3:44 PM, Ekstrand, Pamela A. -ND=20
> <Pamela.A.Ekstrand.-ND@disney.com> wrote:=20
>>=20
>> All,=20
>>=20
>> I am being asked if there is a way to export the contents of tables =
in an=20
>> IDS10.0.FC4 database to a .csv format file. Does anyone know if this =
is=20
>> available?=20
>>=20
>> Thanks.=20
>>=20
>> Pam=20
>>=20
>>=20
>>=20
>=20
> =
**************************************************************************=
*****=20
>> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>>=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Unload to file delimiter ","
Select * from somewhere
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Ekstrand, Pamela A. -ND
Sent: Friday, February 23, 2018 2:44 PM
To: ids@iiug.org
Subject: Unloading Data to .csv Format [40757]
All,
I am being asked if there is a way to export the contents of tables in an
IDS10.0.FC4 database to a .csv format file. Does anyone know if this is
available?
Thanks.
Pam
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
That proposed solution (DBDELIMITER=, and unload statements) is not very
close to standard CSV format. It may or may not be accepted by whatever
software is using the data. If the output is accepted, then great â youâre
done.
If it doesnât work and youâre willing to compile code, my SQLCMD (available
from the IIUG website) has a more orthodox CSV format for output. It will
enclose string-like fields in double quotes and double up double quotes
within fields, rather than escaping them with backslashes.
At one time, there was a pair of Perl scripts called csv2unl and unl2csv
for converting between unload format and CSV format. Iâm not sure if
theyâre available at the IIUG. If not, I can provide them (and maybe it is
time to make sure that they are available at the IIUG).
On Fri, Feb 23, 2018 at 13:06 Ekstrand, Pamela A. -ND <
Pamela.A.Ekstrand.-ND@disney.com> wrote:
> Ah, ok, thanks much.
>
> ________________________________________
> From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of Jack Parker
> [jack.parker4@verizon.net]
> Sent: Friday, February 23, 2018 3:59 PM
> To: ids@iiug.org
> Subject: Re: Unloading Data to .csv Format [40760]
>
> The commas will get =E2=80=9Cescaped=E2=80=9D. Like this:
> ABC\\\\,DEF\\\\,GHJ,
>
> cheers
> j.
>
> > On Feb 23, 2018, at 3:55 PM, Ekstrand, Pamela A. -ND =
> <Pamela.A.Ekstrand.-ND@disney.com> wrote:
> >=20
> > Right, I was just concerned about the possibility of commas imbedded =
> within=20
> > the data.=20
> >=20
> > ________________________________________=20
> > From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of Jack =
> Parker=20
> > [jack.parker4@verizon.net]=20
> > Sent: Friday, February 23, 2018 3:51 PM=20
> > To: ids@iiug.org=20
> > Subject: Re: Unloading Data to .csv Format [40758]=20
> >=20
> > Sure.=20
> >=20
> > export DBDELIMITER=3D,=20
> >=20
> > from SQL:=20
> > unload to <yourfile> select * from <table>=20> >=20
> > A simplistic answer, if you have a lot of data, you should consider =
> external=20
> > tables, but you can specify a delimiter for that as well.=20
> >=20
> > cheers=20
> > j.=20
> >=20
> >> On Feb 23, 2018, at 3:44 PM, Ekstrand, Pamela A. -ND=20
> > <Pamela.A.Ekstrand.-ND@disney.com> wrote:=20
> >>=20
> >> All,=20
> >>=20
> >> I am being asked if there is a way to export the contents of tables =
> in an=20
> >> IDS10.0.FC4 database to a .csv format file. Does anyone know if this =
> is=20
> >> available?=20
> >>=20
> >> Thanks.=20
> >>=20
> >> Pam=20
> >>=20
> >>=20
> >>=20
> >=20
> > =
> **************************************************************************=
> *****=20
> >> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>
> >>=20
> >=20
> >=20
> > =
> **************************************************************************=
> *****=20
> > Forum Note: Use "Reply" to post a response in the discussion forum.=20
> >=20
> >=20
> > =
> **************************************************************************=
> *****=20
> > Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>
> >=20
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> --
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2015.1101 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
Jonathan, you hit upon my concern about the standard .csv format.
I am not sure if just using the comma as the delimiter will be acceptable, but
I will check with the customer.
I would be interested to see if the unl2csv script would be available.
________________________________________
From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of Jonathan
Leffler [jonathan.leffler@gmail.com]
Sent: Friday, February 23, 2018 4:28 PM
To: ids@iiug.org
Subject: Re: Unloading Data to .csv Format [40763]
That proposed solution (DBDELIMITER=, and unload statements) is not very
close to standard CSV format. It may or may not be accepted by whatever
software is using the data. If the output is accepted, then great youre
done.
If it doesnt work and youre willing to compile code, my SQLCMD
(available
from the IIUG website) has a more orthodox CSV format for output. It will
enclose string-like fields in double quotes and double up double quotes
within fields, rather than escaping them with backslashes.
At one time, there was a pair of Perl scripts called csv2unl and unl2csv
for converting between unload format and CSV format. Im not sure if
theyre available at the IIUG. If not, I can provide them (and maybe it is
time to make sure that they are available at the IIUG).
On Fri, Feb 23, 2018 at 13:06 Ekstrand, Pamela A. -ND <
Pamela.A.Ekstrand.-ND@disney.com> wrote:
> Ah, ok, thanks much.
>
> ________________________________________
> From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of Jack Parker
> [jack.parker4@verizon.net]
> Sent: Friday, February 23, 2018 3:59 PM
> To: ids@iiug.org
> Subject: Re: Unloading Data to .csv Format [40760]
>
> The commas will get =E2=80=9Cescaped=E2=80=9D. Like this:
> ABC\\\\,DEF\\\\,GHJ,
>
> cheers
> j.
>
> > On Feb 23, 2018, at 3:55 PM, Ekstrand, Pamela A. -ND =
> <Pamela.A.Ekstrand.-ND@disney.com> wrote:
> >=20
> > Right, I was just concerned about the possibility of commas imbedded =
> within=20
> > the data.=20
> >=20
> > ________________________________________=20
> > From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of Jack =
> Parker=20
> > [jack.parker4@verizon.net]=20
> > Sent: Friday, February 23, 2018 3:51 PM=20
> > To: ids@iiug.org=20
> > Subject: Re: Unloading Data to .csv Format [40758]=20
> >=20
> > Sure.=20
> >=20
> > export DBDELIMITER=3D,=20
> >=20
> > from SQL:=20
> > unload to <yourfile> select * from <table>=20> >=20
> > A simplistic answer, if you have a lot of data, you should consider =
> external=20
> > tables, but you can specify a delimiter for that as well.=20
> >=20
> > cheers=20
> > j.=20
> >=20
> >> On Feb 23, 2018, at 3:44 PM, Ekstrand, Pamela A. -ND=20
> > <Pamela.A.Ekstrand.-ND@disney.com> wrote:=20
> >>=20
> >> All,=20
> >>=20
> >> I am being asked if there is a way to export the contents of tables =
> in an=20
> >> IDS10.0.FC4 database to a .csv format file. Does anyone know if this =
> is=20
> >> available?=20
> >>=20
> >> Thanks.=20
> >>=20
> >> Pam=20
> >>=20
> >>=20
> >>=20
> >=20
> > =
> **************************************************************************=
> *****=20
> >> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>
> >>=20
> >=20
> >=20
> > =
> **************************************************************************=
> *****=20
> > Forum Note: Use "Reply" to post a response in the discussion forum.=20
> >=20
> >=20
> > =
> **************************************************************************=
> *****=20
> > Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>
> >=20
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> --
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2015.1101 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.