Solved: How can i manage csv files from python query

Anonymous · ‎08-30-2018

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.

Anonymous · ‎09-04-2018

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

View solution in original post

v-yuezhe-msft · ‎08-30-2018

@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,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎08-31-2018

Hi:

This is the code that i use. Try it:

import csv
import requests

URL = '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 = 0

files = []
files_index = 0

for 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 blanco

file_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 += 1

crw = 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()

v-yuezhe-msft · ‎09-03-2018

@Anonymous,

Are you able to run the above python file in Python Shell? I get same error in Python Shell and Power BI Desktop.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎09-03-2018

Sorry, i put code without the correct tool.

import csv
import requests

URL = '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 = 0

files = []
files_index = 0

for 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 blanco	

			file_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 += 1

			crw = 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()

Run it and introduce code:

Finally, downloaded files can`t detect semicolon delimiters.

v-yuezhe-msft · ‎09-04-2018

@Anonymous,

I import the table into Power BI using your python script and get the following error.

I suspect the issue is caused by the special format of the CSV file, you can take a look at the following similar thread.

https://social.technet.microsoft.com/Forums/en-US/1170ca42-695c-491b-9401-4c1cefe92a7b/how-to-ignore-the-first-row-in-a-csv-import?forum=powerquery

As a workaround, I would recommend you directly connect to these csv files in Power BI Desktop, or convert the csv files to Excel in Python script, then check if the issue still persists.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎09-04-2018

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

v-yuezhe-msft · ‎09-05-2018

@Anonymous,

Glad to hear the the issue is solved. You can accept your reply as answer as solution to close this thread.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

How can i manage csv files from python query

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024