3
$\begingroup$

pd is a panda module is one way of reading excel but its not available in my cluster. I want to read excel without pd module. Code1 and Code2 are two implementations i want in pyspark.

Code 1: Reading Excel

pdf = pd.read_excel(Name.xlsx)
sparkDF = sqlContext.createDataFrame(pdf)
df = sparkDF.rdd.map(list)
type(df)

Want to implement without pandas module

Code 2: gets list of strings from column colname in dataframe df

stringsList = []
columnList = list(df[colname])
for i in range(len(columnList)):
    if type(columnList[i]) != float:
        text = columnList[i]
        stringsList.append(text.lower())    
    else:
        stringsList.append(u'')
return stringsList

I want to implement this in pyspark.

Improve this question
$\endgroup$
2
  • 2
    $\begingroup$ Why don't you convert the Excel files before uploading them to the cluster? If you are allowed to install libraries there's github.com/crealytics/spark-excel $\endgroup$
    – Emre
    Aug 31 '17 at 19:20
  • $\begingroup$ It does not depend on me its requirement.They want data from excel only.Thanks for response @Emre $\endgroup$
    – shalu
    Sep 1 '17 at 10:00
1
$\begingroup$

Is pandas itself available on the cluster? If so, you may try to go with the in-built read_excel().

You may also try the HadoopOffice library, it contains a Spark DataSource, also available as Spark Package, you can easily test it out without any installation:

$SPARK_HOME/bin/pyspark --packages com.github.zuinnote:spark-hadoopoffice-ds_2.11:1.0.4

Some people also recommend the Spark Excel dependency.

Improve this answer
$\endgroup$
4
  • $\begingroup$ Pandas not available in cluster.Will try with hadoopOffice but I am not not sure whether I can use it or not. $\endgroup$
    – shalu
    Feb 27 '18 at 7:03
  • $\begingroup$ The Spark Excel I mentioned doesn't require installation too, e.g.: $SPARK_HOME/bin/spark-shell --packages com.crealytics:spark-excel_2.11:0.9.12 $\endgroup$
    – Dominik
    Feb 27 '18 at 11:16
  • $\begingroup$ Is there any way without any installation I can do this. $\endgroup$
    – shalu
    Jul 12 '18 at 11:55
  • $\begingroup$ Please take a look at my previous comment, right above yours :] $\endgroup$
    – Dominik
    Jul 13 '18 at 8:34
1
$\begingroup$

You need the jar crealytics. Use the link - jar to download the jar

Try this, it would help!

def get_df_from_excel(sqlContext, file_name):
    """    
    This method is intended to create a dataframe form excel file
    :param sqlContext: sqlContext
    :param file_name:  - Address of file 
    :return: dataframe
    """
    return sqlContext.read.format("com.crealytics.spark.excel") \
        .option("useHeader", "true") \
        .option("treatEmptyValuesAsNulls", "true") \
        .option("inferSchema", "true") \
        .option("addColorColumns", "False") \
        .option("maxRowsInMey", 2000) \
        .option("sheetName", "Import") \
        .load(file_name)
Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.