Replace columns indexes reading a XSLX file

[Larry1888]

Hi all!

I am trying to make my code read certain indexes of an excel file depending on the type of configuration it has in the database, for example:

This is the table that im using to store the config

ClaveCliente TipoLayout BienesTransp Descripcion Cantidad ClaveUnidad Unidad MaterialPeligroso ....
23825 B 15 14 13 12 11 10 ....
23825 A 36 40 1 65 9 62 ....

If the user selects the type of layout = A in the file, it will read the columns configured in the table, which is where those values ​​would be found in the excel file.

Im using the usecols parameter to store the columns, but instead of reading those columns in the file is taking only the first 6 columns of the file

This is the python code that im using:

    conn = pyodbc.connect(
   [b]DB CONNECTION.....[/b]
    )
    cursor3 = conn.cursor()
    
    #here i store the TipoLayout that is gonna be used
    layout = request.POST["tipLayoutdropdown"]
    
    #this query returns the columns configured
    cursor3.execute(
        """ select BienesTransp,
                    Descripcion,
                    Cantidad,
                    ClaveUnidad,
                    Unidad,
                    MaterialPeligroso,
                    CveMaterialPeligroso,
                    Embalaje,
                    DescripEmbalaje,
                    PesoEnKg,
                    ValorMercancia,
                    Moneda,
                    FraccionArancelaria,
                    UUIDComercioExt,
                    Pedimentos
                    from ForaneosLayout 
                    where TipoLayout = '""" + layout + """'""")
    columns = [column[0] for column in cursor3.description]
    listaqq1 = []
    for i in cursor3.fetchall():
        #listaqq1.append(dict(zip(columns, i))) #Aqui trae tanto el nombre de la columna (columns) y el valor (i) dentro de un diccionario
        #listaqq1.append(dict(zip(columns))) # Aqui trae el nombre de la columna (columns) dentro de un diccionario
        listaqq1.append(i) # Aqui trae el valor (i)
    usecols1 = list(listaqq1[0])
    
    # Load the xlsx file
    #excel_data = pd.read_excel(excel_file, usecols=usecols1).iloc[:, [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]]
    excel_data = pd.read_excel(excel_file, usecols=usecols1)

    pdb.set_trace()
    
    # Read the values of the file in the dataframe
    data = pd.DataFrame(
        excel_data
    )

Another option that i saw on internet was to use the column like ["A", "AB", "CD"], but honestly not sure how i can reach that.

Using the name of the columns is not an option because every layout comes with different tittles in the columns.

Can you please give me an idea of how to fix this?

[deanhystad]

Have you verified that usecols1 actually contains the column header names you want? I don't know what's returned by your query, but this code looks sketchy to me:

 for i in cursor3.fetchall():
     listaqq1.append(i)
usecols1 = list(listaqq1[0])

But even if usecols1 contains the column names, I don't think you want to use read_excel(usecols=usecols1). This will select columns by name, but the order of columns in the dataframe matches the order of the columns in the spreadsheet, not the usecols1 list. Instead of filtering the columns when you read the spreadsheet, read the entire spreadsheet and select the columns you want in the order you want.

import pandas as pd

# Making a spreadsheet file
df = pd.DataFrame({"one":[1, 2, 3], "two":[2, 4, 6], "three":[3, 6, 9]})
df.to_excel("test.xlsx", index=None)

# Read in the spreadsheet file
df2 = pd.read_excel("test.xlsx")
print(df2)

# Select the columns you want using the column index.  Could also use column names if you prefer.
df3 = df2.iloc[:, [2, 1]]
df3.columns = ["A", "B"] # Rename the columns if you want.
print(df3)

   one  two  three
0    1    2      3
1    2    4      6
2    3    6      9
   A  B
0  3  2
1  6  4
2  9  6

[Pedroski55]

How about you export the first ten rows of the table as .sql and post that here?

Then anyone can try what you want to do and see what is wrong. ¿vale?