Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

i have records in csv file like this, how can i remove double quotes and comma by writing hive query table and how can i load the data into hive table?

Highlighted

i have records in csv file like this, how can i remove double quotes and comma by writing hive query table and how can i load the data into hive table?

Explorer

"75765","PH",100318,"PHAREX HEALTH CORP","13011522","PHAREX CLARITHROMYCIN500MG 50'S TAB","CLARIT","CLARIT","TAB","Tablets","500","500mg","J","General Anti-Infectives Systemic","1","Systemic Antibacterials","F","Macrolides and Similar Types","0","Macrolides and similar types","ZET","Ethical Item","30009722","ROSE PHARMACY-CALAMBA","NATIONAL HIGHWAY","4027 LAGUNA-CALAMBA",NA,NA,NA,141,"ROSE PHARMACY-CALAMBA","NATIONAL HIGHWAY","4027 LAGUNA-CALAMBA",NA,NA,NA,NA,NA,"RT1","Retail Trade","RT1PH2","RT Pharmacy","RT102","Chain Pharmacy","CD","PH:CHAIN DRUGSTORES","A4A","CHAIN DRUGSTORES","A4A","CHAIN DRUGSTORES","PH:CHAIN DRUGSTORES","NULL","NULL","Laguna",NA,"Calamba",NA,"MA11","Batangas","IV","NULL","NULL",NA,NA,NA,NA,NA,NA,1030317079,780289,20130323,1,0,0,0,41.2873173,36.86367622,41.2873173,36.86367622,41.2873173,36.86367622,NA,41.28731738,36.8636762,41.28731738,36.8636762,41.287317,36.863676,0,0

5 REPLIES 5
Highlighted

Re: i have records in csv file like this, how can i remove double quotes and comma by writing hive query table and how can i load the data into hive table?

this command can be useful to remove all the double qoutes, it can be enhanced further to remove comma as well

sed s"/\"//g" sample.csv | sed 's/ //g'

,

use this command to remove all double qoutes, the same logic you can apply for comma

sed s"/\"//g" file.csv | sed 's/ //g'

Re: i have records in csv file like this, how can i remove double quotes and comma by writing hive query table and how can i load the data into hive table?

Explorer

Hi Rajkumar, i am new for this, where can i mention above code?

Highlighted

Re: i have records in csv file like this, how can i remove double quotes and comma by writing hive query table and how can i load the data into hive table?

this is just a sanity work, as a part of data preparation you can do it and then load data into the hive tables.

Highlighted

Re: i have records in csv file like this, how can i remove double quotes and comma by writing hive query table and how can i load the data into hive table?

The Hive CSV Serde supports quotes. And its part of the normal installation now. Just put it into hdfs create an external table using it and then transform it into something faster ( ORC )

https://cwiki.apache.org/confluence/display/Hive/CSV+Serde

Highlighted

Re: i have records in csv file like this, how can i remove double quotes and comma by writing hive query table and how can i load the data into hive table?

Hi @prswamy t. I agree with @Benjamin Leonhardi, CSV Serde is a great option. Here is a writeup about CSV Serde with some examples.

https://community.hortonworks.com/articles/2834/when-to-use-and-when-not-to-use-hive-csvserde.html.

With your example file, you would use SeparatorChar = comma and QuoteChar = double quotes. (which are the CSV Serde defaults by the way).

Don't have an account?
Coming from Hortonworks? Activate your account here