CODEWITHSUNDEEP
bulkinsertsqlanywherebulk-loadsybase-asa
Aditya Dhanraj
Aditya Dhanraj

Reputation: 199

is it possible to upload ' ,' delimitted file in single column of sybase DB

I have , delimitted text file.

How to upload this text file in a single column of a sybase table.

I tried this but got error

LOAD TABLE VOD_K
(telecastKey)
FROM
'C://Users//1336856//Documents//VOD.txt'
STRIP OFF
ESCAPES OFF
QUOTES OFF
ROW DELIMITED BY ','

error : The row delimiter must be distinct from all column delimiters.

Here is my text file :

enter image description here

Upvotes: 2

Views: 682

Answers (1)

markp-fuso
markp-fuso

Reputation: 35256

Assuming this is Sybase IQ (or SQLAnywhere) ...

  • the default column delimiter is the comma (,)
  • the default column delimiter can be set via the DELIMITED BY 'some_character' clause ('some_character' can also be a hex code)
  • pick a character that doesn't show up in the data and use is as a column delimiter (yeah, same thing underscore_d mentioned in the comment)

For example, assume a tab (\x09) does not exist in the source data file:

LOAD TABLE VOD_K
(telecastKey)
FROM
'C://Users//1336856//Documents//VOD.txt'
STRIP OFF
ESCAPES OFF
QUOTES OFF
DELIMITED BY '\x09'       <<<<==== set column delimiter to a tab
ROW DELIMITED BY ','

In response to the comment about a new error (invalid size too large); this is likely due to the '\x09' string not being read properly by the database engine), and I'm guessing this may have something to do with the OP running on windows (possible character set issue?).

NOTE: I don't normally work with IQ or SQLAnywhere databases, but when I do it's on unix/linux; so not 100% sure of the issue other than trying a few different things ...

The following was run against a SQL Anywhere database (version 17.0.7.3382) running on a linux host:

# Sample data file:

$ cd /tmp
$ echo "123456,234567,345678,456789,567890" > data.txt
$ cat data.txt
123456,234567,345678,456789,567890

# the following was run via a isql session against the SQL Anywhere db:

create table VOD_K
(telecastKey int not null)
go

LOAD TABLE VOD_K
(telecastKey)
FROM
'/tmp/data.txt'
STRIP OFF
ESCAPES OFF
QUOTES OFF
DELIMITED BY '\x09'
ROW DELIMITED BY ','
go
(5 rows affected)

select * from VOD_K
go

 telecastKey
 -----------
      123456
      234567
      345678
      456789
      567890

Keep in mind that the DELIMITED BY '\x09' clause was an example where we just need to pick a character ... ANY character ... that doesn't exist in the data file; if the data file only contains numbers (0-9) and commas (,), try some other character as a delimiter, eg:

LOAD TABLE VOD_K
(telecastKey)
FROM
'/tmp/data.txt'
STRIP OFF
ESCAPES OFF
QUOTES OFF
DELIMITED BY 'X'       -- use single character 'X'
ROW DELIMITED BY ','
go
(5 rows affected)

select * from VOD_K
go

 telecastKey
 -----------
      123456
      234567
      345678
      456789
      567890

Upvotes: 1

Related Questions