'extra data after last expected column psycopg2

I'm losing my sanity after this error, I've uploaded dozens of tables before, but this one keep giving me the error in the title. I have a data-frame with 11 columns and an SQL table already set up for it. All columns match name.

df_rates = df_rates.replace('\t', '', regex=True)
data_to_upload_output = io.StringIO() # Create object to store csv output in
df_rates.to_csv(data_to_upload_output, sep='\t', header=False, index=False,date_format='%Y-%m-%d') # Send my_data to csv
data_to_upload_output.seek(0) # Return to start of file

conn = psycopg2.connect(host='xxxxx-xxx-x-x',
                       dbname='xxxx',
                       user=uid,
                       password=pwd,
                       port=xxxx,
                       options="-c search_path=dbo,development")
db_table = 'sandbox.gm_dt_input_dist_rates'
with conn:
    with conn.cursor() as cur:        
        cur.copy_from(data_to_upload_output, db_table, null='', columns=my_data.columns) # null values become '', columns should be lowercase, at least for PostgreSQL
conn.commit()
conn.close()

The error continues saying:

CONTEXT: COPY gm_dt_input_dist_rates, line 43: "IE00B44CGS96 USD 0.9088 0.9088 10323906 97.2815 97.2815 2022-05-12 2022-05-11 cfsad 2022-05-20"

Which makes me think the "/t" hasn't been recognized. But this same code works perfectly for all the other tables I'm uploading. I've checked for posts with same errors but I couldn't find a way to apply the solution they got to what I'm experiencing.

Thanks for your help! It is much appreciated, have a great weekend!

pythonpandaspostgresqlpowershellactive-directory


Solution 1:[1]

I'm assuming that with parameter $DomainName you actually mean $GroupName.

When you only need two properties returned, do not use -Properties * because when asking for ALL properties, you are wasting time and memory.

Get-ADGroupMember can return objects of type computer, group or user, and because you only want users, you should filter the result.

Try

# get all user objects that are member of the group and collect the data in variable $result
$result = Get-ADGroupMember -Identity $GroupName |    # get the group of users specified
    Where-Object { $_.objectClass -eq 'user' } |      # filter users only (skip groups and computer objects
    ForEach-Object {
        # Get-ADUser by default returns these properties"
        # DistinguishedName, Enabled, GivenName, Name, ObjectClass, ObjectGUID, SamAccountName, SID, Surname, UserPrincipalName
        Get-ADUser -Identity $_.DistinguishedName -Properties Description | 
        # select and output the wanted properties as object. 
        # this will be collected in variable $result
        Select-Object Name, Description           
    } 

# now you can display on screen
$result | Format-Table -AutoSize

# and export to CSV
$result | Export-Csv -Path 'C:\Users\admin\Desktop\Test.csv' -NoTypeInformation

Solution 2:[2]

Give this a try, first of all, AD Groups can have members other than user objects, you need to filter them. I would also recommend you to not hard code paths (Export-Csv) inside of functions.

function Get-UserInfo{
param(
    [parameter(ValueFromPipeline, Mandatory)]
    [Microsoft.ActiveDirectory.Management.ADGroup]
    $DomainName
)
    
    @(Get-ADGroupMember -Identity $DomainName).Where({
        $_.ObjectClass -eq 'user' # => Groups can have other object types
    }) | Get-ADUser -Properties Description | # => Takes DN from pipeline
    Select-Object Description, Name |
    Export-Csv -NoTypeInformation C:\Users\admin\Desktop\Test.csv
}

Instead of having Export-Csv inside the function you could remove it then you could do something like this with your function:

Get-ADGroup 'somegroup' | Get-UserInfo | Export-Csv path/to/csv.csv

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Theo
Solution 2

Related Questions