Forum Discussion
How can i manage csv files from python query
Hi:
I import csv files using python script but data uses semicolon delimiters and power bi is configuerd with comma. In source properties only edit the script and can`t configure that condition. How can i do?
Than you.
Ok, thank you.
Finally i resolve with comma delimiters. Works ok:
.............................. for row in csvfiler: if row['PAIS'] != 'Total': row['TARIC'] = files[files_index]['CODPROD'] row['ACCION'] = files[files_index]['IE'] csvfilew.writerow(row) filew.close() filer.close() #Convertir a lista with open('inputData.csv', 'r') as f: filer = csv.reader(f,delimiter=';',lineterminator='\n') filelist = list(filer) f.close() with open('inputData.csv', 'w') as f: filew = csv.writer(f,delimiter=',',lineterminator='\n') filew.writerows(filelist) f.close()Thank you so much.
Regards
Álvaro
7 Replies
Anonymous,
Could you please share a csv example and post the Python query so that I can test?
Also why not directly use the built-in CSV connector in Power BI Desktop? If you want to import multiple CSV files with same structure at a time, you can put these files in a folder and use Folder connector in Power BI Desktop.
Regards,
LydiaHi:
This is the code that i use. Try it:
import csv
import requestsURL = 'http://aduanas.camaras.org/downieespannola.php'
#[IE,ANNO,MONTH,MONTHS,PRODUCT,CODPROD,NACAREA,NACAREACOD,INTAREA,INTAREACOD,LOGIN,PASS]
my_pets = [['E','18','00','%2200%22','TA','0701','PR','','PS','','',''],
['I','18','00','%2200%22','TA','07032000','PR','','PS','','','']]
pet_index = 0files = []
files_index = 0for pet in my_pets:
IE = my_pets[pet_index][0]
ANNO = my_pets[pet_index][1]
MONTH = my_pets[pet_index][2]
MONTHS = my_pets[pet_index][3]
PRODUCT = my_pets[pet_index][4]
CODPROD = my_pets[pet_index][5]
NACAREA = my_pets[pet_index][6]
NACAREACOD = my_pets[pet_index][7]
INTAREA = my_pets[pet_index][8]
INTAREACOD = my_pets[pet_index][9]
LOGIN = my_pets[pet_index][10]
PASS = my_pets[pet_index][11]CSV_URL = URL
CSV_URL += '?'
CSV_URL += 'impexp=' + IE
CSV_URL += '&anno=' + ANNO
CSV_URL += '&mes=' + MONTH
CSV_URL += '&tipo=ORGDES'
CSV_URL += '&meses=' + MONTHS
CSV_URL += '&producto=' + PRODUCT
CSV_URL += '&codprod=' + CODPROD
CSV_URL += '&result=DOWN_PS'
CSV_URL += '&orden=LOCAL'
CSV_URL += '&areanacional=' + NACAREA
CSV_URL += '&codareanac=' + NACAREACOD
CSV_URL += '&areainternac=' + INTAREA
CSV_URL += '&codareainter=' + INTAREACOD
CSV_URL += '&login=' + LOGIN
CSV_URL += '&pass=' + PASS
with requests.Session() as s:download = s.get(CSV_URL)
if download.headers['Content-type'] == 'application/csv':
decoded_content = download.content.decode('utf-8')
cr = csv.reader(decoded_content.splitlines(), delimiter=';')
next(cr) #Ignoramos la primera linea en blancofile_name = IE + ANNO + MONTH + PRODUCT + CODPROD + NACAREA + NACAREACOD + INTAREA + INTAREACOD + ".csv"
f = open(file_name,"w")
files.append({'name': file_name, 'IE': IE, 'ANNO': ANNO, 'MONTH': MONTH, 'PRODUCT': PRODUCT, 'CODPROD': CODPROD, 'NACAREA': NACAREA, 'NACAREACOD': NACAREACOD, 'INTAREA': INTAREA, 'INTAREACOD': INTAREACOD})
files_index += 1crw = csv.writer(f,delimiter=';',lineterminator='\n')
crw.writerows(cr)
f.close()pet_index += 1
if files_index > 0:
filew = open('inputData.csv','w')headers = ['PAIS','PESO','VALOR','NUM_OPERACIONES','UNIDADES','TARIC','ACCION']
csvfilew = csv.DictWriter(filew,headers,delimiter=';',lineterminator='\n')
csvfilew.writeheader()filew.close()
while files_index > 0:
files_index -= 1
filer = open(files[files_index]['name'],'r')
filew = open('inputData.csv','a')csvfiler = csv.DictReader(filer,delimiter=';',lineterminator='\n')
csvfilew = csv.DictWriter(filew,headers,delimiter=';',lineterminator='\n')
for row in csvfiler:
if row['PAIS'] != 'Total':
row['TARIC'] = files[files_index]['CODPROD']
row['ACCION'] = files[files_index]['IE']
csvfilew.writerow(row)filew.close()
filer.close()