Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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. ...
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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.