Home Forums Hive / HCatalog Load xls file into Hive Table

Tagged: 

This topic contains 11 replies, has 6 voices, and was last updated by  Bhushan Bhange 2 months, 3 weeks ago.

  • Creator
    Topic
  • #31248

    Anupam Gupta
    Participant

    Hi All,
    I want to know how to load xls file into hive table, my xls file contain comma and double quotes also.

    Thanks in Advance,
    Sandy

Viewing 11 replies - 1 through 11 (of 11 total)

You must be logged in to reply to this topic.

  • Author
    Replies
  • #60961

    Bhushan Bhange
    Participant

    Hi All , Same issue , when I uploaded XLS file using HCatalog -> Create table using Excel file -> XLS file
    In Table preview it does not show NULL values but once create table is done. When I check it shows NULL values
    in Excel only 500 Rows but it shows 6000 rows with so many NULL values though there is no NULL in excel file . Any body knows what should I do ?

    Collapse
    #49745

    Yang Guang
    Participant

    fkdsajjefjklfkdljfklsdajfioewjflkdsjflkerjfkjefwe

    Collapse
    #43678

    Anupam Gupta
    Participant

    Hi Zhang,
    Thanks for your reply, I create hive table using following query..

    create table csvdata(InteractionId
    string, FromUserWatch
    string, FromTermWatch
    string,Username
    string,UserUrl
    string,UserSummary
    string,AvatarImageUrl
    string,Content
    string,CreatedAtUTC
    string,CreatedAtFormatted
    string,Generator
    string,TweetUrl
    string,Latitude
    string,Longitude
    string)
    row format serde ‘com.bizo.hive.serde.csv.CSVSerde’ stored as textfile;

    then load data into table using…

    LOAD DATA LOCAL INPATH ‘/usr/local/data_file.csv’
    OVERWRITE INTO TABLE csvdata;

    I am getting null on Third row for content column data , after content column all data is null for other column in that row.
    Ex.
    Interaction _Id FromUserWatch Username Content
    (1) 3.60E+17 FALSE @RebeccaShabad RT @TheFix: Amazing. RT @SeinfeldToday: George is briefly implicated in the latest

    (2) 3.60E+17 FALSE @ohbambiemma @santwix “I’d like mine to mean something,” Emma stroked her skin, her eyes closing. Hearing a car pull in the drive way, she frowned. “Is

    (3) 3.60E+17 FALSE @Bernie_Bernz #lunch #nyc #sfilatino adorable ?? sandwich shop @

    Thanks In Advance,
    Sandy

    Collapse
    #42870

    Yi Zhang
    Moderator

    Hi Sandy,

    Can you give us some rows that have the data ingested correctly into hive and some rows that aren’t.

    Thanks,
    Yi

    Collapse
    #42753

    Anupam Gupta
    Participant

    Hi Zhang,

    Data showing null for some column in a row (not for all rows) , I have twitter data file (.csv ). I am using csv-serde from bizo available in github.

    Thanks in advance,
    Sandy

    Collapse
    #42742

    Yi Zhang
    Moderator

    Hi Sandy,

    It is possible that the data can not be typed into the data type they are supposed to be, thus the null value.

    Thanks,
    Yi

    Collapse
    #42668

    Anupam Gupta
    Participant

    Hi All,

    My csv file has special characters , double quotes, single quotes ,numbers etc. ,so I am using csv-serde to serialize and deserialize. but I am getting null for some columns data in a row in hive table, what is the problem?
    Kindly help

    Thanks,
    Sandy

    Collapse
    #32574

    Carter Shanklin
    Participant

    Sandy, there is an Excel import feature in Hue in the Hortonworks Sandbox and in the latest version of HDP under the HCatalog app. You can also load CSV there. Can you try that out and see if it works for you?

    Collapse
    #31812

    Anupam Gupta
    Participant

    Hi Sasha,
    Thanks for your Reply, I have a csv file which contain Twitter data I am loading this data into hive table using csv-serde.jar. I able to load data into hive table but when I fetch data using Select query some column and row are showing NULL values.
    Why they are showing NULL I am new to hive please help us.

    Thanks In Advance,
    Sandy

    Collapse
    #31265

    Sasha J
    Moderator

    Loading XLS files directly is not supported yet.
    You have to export it to delimited CSV file and then load it.

    Hope this help.
    Sasha

    Collapse
    #31264

    Sasha J
    Moderator

    Loading XLS files directly is not supported yet.
    You have to export it to delimited CSV file and then load it.

    Hope this help.
    Sasha

    Collapse
Viewing 11 replies - 1 through 11 (of 11 total)