Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Unable to import database from python when using loop to get data set from url

When I try to loop to get the latest back up my month , PowerBI is not importing the table , but if I just use get latest file from url by adding latest to it , so no looping required , it works. each method uses the get response text.

 

this end of my code works fine

raw_input_file = io.StringIO(response.text)
csv_reader = csv.reader(raw_input_file, delimiter=',')

input_rows = []
for row in csv_reader:
if row:
input_rows.append (row)

input_df = pd.DataFrame(input_rows[1:], columns=input_rows[0])
input_df

 

but when I have the loop code it does not import anything

 

raw_input_file = io.StringIO(response.text)

csv_reader = csv.reader(raw_input_file, delimiter=',')

input_rows = []
for row in csv_reader:
if row:
input_rows.append (row)
previous = pd.DataFrame(input_rows[1:], columns=input_rows[0])
prev = pandas.read_csv(previous)
break
except:
pass

get_files_for_month(-1)

 

the get_files_for_month(-1)  function allows me to select which ever previous month I wish to loop. 

 

 

Status: New
Comments
v-yuta-msft
Community Support

@Pandadev ,

 

I'm confused on the part of code you given, is there any syntax error because previous is not a csv file.

previous = pd.DataFrame(input_rows[1:], columns=input_rows[0])
prev = pandas.read_csv(previous)

In addtion, could you please share the complete code so that I can test on my side?

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Pandadev
Post Prodigy

Thanks foryour response. I am unable to show you the full code as it includes my private API and the URL I pull the data from. The way the data we store is structured is taht if it is a latest file , then I can simply add latest to the URL and add my API to retrieve the data . and this code 

raw_input_file = io.StringIO(response.text)
csv_reader = csv.reader(raw_input_file, delimiter=',')

input_rows = []
for row in csv_reader:
if row:
input_rows.append (row)

input_df = pd.DataFrame(input_rows[1:], columns=input_rows[0])
input_df

 

imports a table called input_df to PowerBI.

 

My issue is that I am having to loop in reverse to get the last updated version , as the archived data files include a date at the end of the url instead of latest. but these are not always updated every day. so I could not use last day of previous month. When I tried to import this to PowerBI by adding the code

raw_input_file = io.StringIO(response.text)
csv_reader = csv.reader(raw_input_file, delimiter=',')

input_rows = []
for row in csv_reader:
if row:
input_rows.append (row)

input_df = pd.DataFrame(input_rows[1:], columns=input_rows[0])
input_df

 

There is no error , but no data is added.

I thought it maybe because of the additional lines of code shown below as this is the only difference. If I run the code and in python module , and add print (input_df) it prints the data.

break
except:
pass

get_files_for_month(-1)

Pandadev
Post Prodigy

This is the full file , with the api key and url amended , this was the only way I could loop through each day of the month. the code looks to see if the response contains data after the word uris. if the text has more than 15 characters then this is the correct url to get the data , add apikey key to it and it gets the data. convert this to a dataframe.

 

from datetime import date
import datetime
import calendar
import requests
import csv
import io
from tkinter.filedialog import askopenfilename, Tk
from tkinter import messagebox
import pandas as pd
import xlrd as xl


def monthdelta(date, delta):

m, y = (date.month+delta) % 12, date.year + ((date.month)+delta-1) // 12
if not m: m = 12
d = min(date.day, [31,
29 if y%4==0 and not y%400==0 else 28,31,30,31,30,31,31,30,31,30,31][m-1])
return date.replace(day=d,month=m, year=y)

# looping months in reverse order

def get_files_for_month(m):
month_index = monthdelta(datetime.date.today(), m)
_, date_index = calendar.monthrange(month_index.year,month_index.month)

# looping dates of month from last day to first day
for i in reversed(range(1,date_index+1)):
d = datetime.datetime(month_index.year, month_index.month, i).strftime("%Y/%m/%d")
#print(d)
try:

#print(d)
marketplace_apikey ='123456'
response = requests.get('https://***url***/'+d+market_place_key)

test_string =(response.content)
spl_word = 'uris" : [ "'
textstr = str(test_string)
textstr = textstr.partition(spl_word)[2]
rest = textstr
sep = '"'
urlres = rest.split(sep, 1)[0]

if len(urlres)>15 :
response = requests.get(urlres+market_place_key)
raw_input_file = io.StringIO(response.text)

csv_reader = csv.reader(raw_input_file, delimiter=',')

input_rows = []
for row in csv_reader:
if row:
input_rows.append (row)
input_dff = pd.DataFrame(input_rows[1:], columns=input_rows[0])
input_dff

break
except:
pass

get_files_for_month(-1)

Pandadev
Post Prodigy

This code , prints out the response text and also prints out the dataframe input_df , but when I run it in power bi , no table is created.

if len(urlres)>15 :
raw_input_data = requests.get(urlres+market_place_key).text
print (response)
raw_input_data = raw_input_data.replace('[', '').replace(']', '').replace('\'', '')
raw_input_file = io.StringIO(raw_input_data)
csv_reader = csv.reader(raw_input_file, delimiter=',')

# append each non empty row to the list input_rows and convert to a
# pandas dataframe, assume first row is the header
input_rows = []
for row in csv_reader:
if row:
input_rows.append(row)
input_df = pd.DataFrame(input_rows[1:], columns=input_rows[0])
print(input_df)
input_df
break



except:
pass

get_files_for_month(-1)