how to use ascii character for quote in COPY in cqlsh

cassandra cql3 cqlsh csv sqlbulkcopy

Question

I am uploading data from a a big .csv file into Cassandra using copy in cqlsh. I am using cassandra 1.2 and CQL 3.0. However since " is part of my data I have to use some other character for uploading my data, I need to use any extended ASCII characters. I tried various approaches but fails.

The following works, but need to use an extended ascii characters for my purpose..

copy (<columnnames>) from <filename> where deleimiter='|' and quote = '"';
copy (<columnnames>) from <filename> where deleimiter='|' and quote = '~';

When I give quote='ß', I get the error below:

:"quotechar" must be an 1-character string

Pls advice on how I can use an extended ASCII character for quote parameter..

Thanks in advance

1
0
7/18/2013 8:56:49 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

I had a similar problem, and inspected the source code of cqlsh (it's a python script). In my case, I was generating the csv with python, so it was a matter of finding the right python csv parameters.

Here's the key information from cqlsh:

    csv_dialect_defaults = dict(delimiter=',', doublequote=False,
                            escapechar='\\', quotechar='"')

So if you are lucky enough to generate your .csv file from python, it's just a matter of using the csv module with:

    writer = csv.writer(open("output.csv", 'w'), **csv_dialect_defaults)

Hope this helps, even if you are not using python.

0
9/12/2014 11:43:04 AM

Related Questions

Strings longer than 255 characters are not copied by SqlBulkCopy.

I am not aware of SqlBulkCopy and OldDbConnection.... however Excel might be giving out first 255 chars.... In Excel if you access a cells value with .value property you get first 255 chars, and if you acccess it with .text property you get full text. Hope this will give you some idea.
excel sqlbulkcopy
asked by Adarsha

How to use SQL bulk copy to retrieve the IDs of added data records

I'm currently doing something like this:...DataTable objects = new DataTable(); DataColumn keyColumn = new DataColumn("name", typeof(string)); DataColumn versionColumn = new DataColumn("version", typeof(int)); versionColumn.DefaultValue = iVersionID; objects.Columns.Add(keyColumn); objects.Columns.Add(versionColumn); foreach (KeyValuePair<string,...
ado.net database identity sqlbulkcopy sql-server
asked by Timbo

Is it possible to transfer data across servers using sql bulk copy?

Yes. In fact, in the demo code shown ...here..., they copy to a table within the same database.
sql sqlbulkcopy sql-server
asked by Joe Stefanelli

ASP.Net to MS-Access 2007 conversion (Bulk Insert, Bulk Copy)

Access 2007 will directly import XML. You can create an XSD for your particular data, then import that to create the table definitions. The XML should cleanly import from there.
asp.net sqlbulkcopy vb.net xml
asked by Karelzarath

When SQLBulkCopy is used with Transaction, an event is fired each time a batch is copied.

I have finally decided to use an internal transaction. ATM it seems to be working fine if an error is encountered the current batch is rolled back. Regarding Ben's comment above, i tried this but its not possible, its not as though it loops through a section of code where you could raise an event. In the end i used the notifyafter property, which r...
c# sqlbulkcopy sql-server
asked by Hans Rudel

Using file stream, sql bulk copy will not upload data to the database.

Simplify your code; there are plenty of ways to get the csv into a datatable - see ...How to read a CSV file into a .NET Datatable... for example. In your example above, you do not appear to be closing the stream after you have finished reading it... add sr.Close() before the return, or better still wrap the declaration in a using() statement:... ...
asp.net c# sqlbulkcopy
asked by dash

"ERROR: extra data after last expected column" when using PostgreSQL COPY

An empty table won't do. You need table that matches the structure of input data. Something like:...CREATE TABLE raw_data ( col1 int , col2 int ... ); ...You don't need to declare ...tab... as ...DELIMITER... since that's the default:...COPY raw_data FROM '/home/Projects/TestData/raw_data.txt'; ...800 columns you say? That many columns would ty...
database-design dynamic-sql postgresql sqlbulkcopy
asked by Erwin Brandstetter

Using the SQL bulk copy in C# and deleting the string headers

Try this : ...string format = "MM/dd/yyyy hh:mm:ss.fff"; DateTime d =DteTime.ParseExact(s[6],format,System.Globalization.CultureInfo.InvariantCulture); dr["DateTime"]=d; ...Change the format as you want like "MM/dd/yyyy" or "MM/dd/yyyy hh:mm" etc
c# datetime sql sqlbulkcopy sql-server
asked by tHiNk_OuT_oF_bOx

Missing Data after insert into my end sql table using Sql Bulk Copy

you need to look at the data in Access, and find/fix those bad column values, or handle them in your TSQL after the export. One option to debug - replace temp table with real table, replace datetime datatype with string, do your export, then query the table to find the bad values so you know what kind of data to look for. After that, you can swit...
c# console-application datatable sqlbulkcopy sql-server-2008
asked by Greg

Using SQL Bulk Copy to insert ~3.7 million rows throws OutOfMemory exception

Thank you all for your response....I figured out what was the problem. Firstly, it didn't occur when calling the SQL Bulk Copy, which I assumed it could have been because, in previous run, I was able to run few 1000 less records than the current one, so I thought the SQL Bulk Copy had limitations or need to settings in order to handle large number ...
c# sqlbulkcopy sql-server
asked by superachu

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow